﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

using System.Text;
using System.Web.Configuration;

using Recon.Portal.DataObjects;

namespace Recon.Portal.Data
{
    public class DBFactory
    {
        private SqlConnection conn = null;

        public enum ReportType
        {
            Invoice,
            Statement,
            Both
        }
        public DBFactory()
        {
            object connString = WebConfigurationManager.ConnectionStrings["SqlConnection"];
            if (connString != null)
            {
                conn = new SqlConnection(connString.ToString());
            }
        }

        public int AddCustomer(Customer cust, string userID)
        {
            int retval = 0;

            try
            {
                string strQuery = "insert into mast_customer_maint(customer_id,customer_name, customer_address1, customer_address2," +
                    "customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled,is_active," +
                    "addby_id,addby_datetime" +
                    ") values ('" + cust.CustomerID + "','" + cust.CustomerName + "','" + cust.Address1 + "','" + cust.Address2 + "'," +
                    "'" + cust.City + "','" + cust.State + "','" + cust.Country + "'," +
                    "'" + cust.ZipCode + "','" + cust.TelephoneNumber + "','" + cust.EmailAddress + "'," +
                    "" + (cust.IsEnabled ? 1 : 0) + ",'1','" + userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')";
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                conn.Open();
                retval = cmd.ExecuteNonQuery();

                //create 2 default customer admin users - user id = admin1/2", password generated randomly
                string tempPwd1 = Utils.EncryptValue(Utils.GenerateRandomPassword());
                string tempPwd2 = Utils.EncryptValue(Utils.GenerateRandomPassword());
                while (tempPwd1 == tempPwd2)
                {
                    tempPwd2 = Utils.EncryptValue(Utils.GenerateRandomPassword());
                }
                strQuery = "insert into mast_user_maint(User_Id,User_Type,Remitter_Id,Customer_Id," +
                "User_Name,User_Password,Role_Id,Email_Id,Mobile_No,Is_Enabled,Is_LoggedIn," +
                "Is_Locked,Is_Active,Login_Attempt,LatLogin_DateTime,Password_ExpiryDate," +
                "AddBy_UserId,AddBy_Date,CurrentLogin_Datetime,Is_DomainUser) values (" +
                "'admin1','C',NULL,'" + cust.CustomerID + "'," +
                "'Admin 1','" + tempPwd1 + "','CUSTADM','" + cust.EmailAddress + "','" + cust.TelephoneNumber + "',1,0," +
                "0,1,'0','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + System.DateTime.Now.AddDays(90).ToString("yyyy-MM-dd") + "'," +
                "'" + userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',0)," + 
                "('admin2','C',NULL,'" + cust.CustomerID + "'," +
                "'Admin 2','" + tempPwd2 + "','CUSTADM','" + cust.EmailAddress + "','" + cust.TelephoneNumber + "',1,0," +
                "0,1,'0','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + System.DateTime.Now.AddDays(90).ToString("yyyy-MM-dd") + "'," +
                "'" + userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',0)";

                cmd.CommandText = strQuery;
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int RegisterUser(User user, string userID)
        {
            int retval = 0;

            try
            {
                string tempPwd = Utils.GenerateRandomPassword();
                conn.Open();
                SqlCommand cmd = new SqlCommand("insert into mast_user_maint(User_Id,User_Type,Remitter_Id,Customer_Id," +
                    "User_Name,User_Password,Role_Id,Email_Id,Mobile_No,Is_Enabled,Is_LoggedIn," +
                    "Is_Locked,Is_Active,Login_Attempt,LatLogin_DateTime,Password_ExpiryDate," +
                    "AddBy_UserId,AddBy_Date,CurrentLogin_Datetime,Is_DomainUser, Is_ForcedPwdChange) values (" +
                    "'" + user.UserID + "','" + user.UserType + "','" + (string.IsNullOrEmpty(user.RemitterID)?string.Empty:user.RemitterID) + "','" + user.CustomerID + "'," +
                    "'" + user.UserName + "','" + Utils.EncryptValue(tempPwd) + "','" + user.RoleID + "','" + user.EmailAddress + "','" + user.MobileNumber + "'," + (user.IsEnabled ? 1 : 0) + "," + 0 + "," +
                    "" + (user.IsLocked ? 1 : 0) + "," + (user.IsActive ? 1 : 0) + ",'0','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + System.DateTime.Now.AddDays(90).ToString("yyyy-MM-dd") + "'," +
                    "'" + userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "'," + (user.IsDomainUser ? 1 : 0) + ",1)", conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int AddStatement(Statement stmt)
        {
            int retval = 0;
            string strQuery = "insert into pro_statement(customer_id,bank_id, statement_date, posting_date,value_date," +
                "tran_type,narration1,narration2,ref_no_or_cheque_no,reconciled_amount," +
                "statement_status,is_partial" +
                ") values('" + stmt.CustomerID + "','" + stmt.BankID + "','" + stmt.statementDate.ToString("yyyy-MM-dd") + "','" + stmt.postingDate.ToString("yyyy-MM-dd") + "'," +
                "'" + stmt.valueDate.ToString("yyyy-MM-dd") + "','" + stmt.transactionType + "','" + stmt.narration1 + "'," +
                "'" + stmt.narration2 + "','" + stmt.referenceNumber + "'," + stmt.ReconciledAmount + ",'" + stmt.Status + "'," + (stmt.IsPartial ? 1 : 0) + ")";


            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        //public int AddDiscountPenalty(string discountPenalty,string customerId ,string effectiveDate, int daySatrt, int dayEnd, double percentage, double minAmount, double maxAmount)
        public int AddDiscountPenalty(DiscountPenalty discountPenalty)
        {
            int retval = 0;
            int existCount = 0;
            //string dicountPenaltyCode = "";

            string strQuery = "";
            if (discountPenalty.discountPenalty == "D")
            {
                strQuery = "SELECT count(discount_code) FROM mast_discount_maint m where customer_id ='" + discountPenalty.customerId + "' " +
                     " and discount_code = '"+ discountPenalty.code +"'";
            }
            else
            {
                strQuery = "SELECT count(panalty_code) FROM mast_panalty_maint m where customer_id ='" + discountPenalty.customerId + "' " +
                     " and panalty_code = '" + discountPenalty.code + "'";
            }
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                existCount = Convert.ToInt32(cmd.ExecuteScalar());

            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            if (existCount == 0)
            {
                if (discountPenalty.discountPenalty == "D")
                {
                    //strQuery = "insert into mast_discount_maint(customer_id,discount_code,effective_date,days_start,days_end,discount_percentage,min_amount,max_amount)" +
                    //    " values('" + discountPenalty.customerId + "','" + discountPenalty.code + "','" + discountPenalty.effectiveDate + "'," +
                    //    "" + discountPenalty.daySatrt + "," + discountPenalty.dayEnd + "," +
                    //    "" + discountPenalty.percentage + "," + discountPenalty.minAmount + "," + discountPenalty.maxAmount + ")";

                    strQuery = "insert into mast_discount_maint(customer_id,discount_code,effective_date,min_amount,max_amount)" +
                       //" values('" + discountPenalty.customerId + "','" + discountPenalty.code + "'," + discountPenalty.effectiveDate.ToShortDateString() + "," +
                        " values('" + discountPenalty.customerId + "','" + discountPenalty.code + "','" + discountPenalty.effectiveDate.ToString("yyyy-MM-dd") + "'," +
                       "" + discountPenalty.minAmount + "," + discountPenalty.maxAmount + ")";
                    
                }
                else
                {
                    //strQuery = "insert into mast_panalty_maint(customer_id,panalty_code,effective_date,days_start,days_end,panalty_percentage,min_amount,max_amount)" +
                    //    " values('" + discountPenalty.customerId + "','" + discountPenalty.code + "','" + discountPenalty.effectiveDate + "'," +
                    //    "" + discountPenalty.daySatrt + "," + discountPenalty.dayEnd + "," +
                    //    "" + discountPenalty.percentage + "," + discountPenalty.minAmount + "," + discountPenalty.maxAmount + ")";

                    strQuery = "insert into mast_panalty_maint(customer_id,panalty_code,effective_date,min_amount,max_amount)" +
                        " values('" + discountPenalty.customerId + "','" + discountPenalty.code + "','" + discountPenalty.effectiveDate.ToString("yyyy-MM-dd") + "'," +
                        "" + discountPenalty.minAmount + "," + discountPenalty.maxAmount + ")";
                }

                try
                {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand(strQuery, conn);
                        retval = cmd.ExecuteNonQuery();
                        conn.Close();
                }
                catch (Exception exc)
                {
                    if (exc != null)
                    {
                        throw;
                    }
                }
                finally
                {
                    conn.Close();
                }
            }

            try
            {
                    strQuery = "insert into conf_DiscountPenalty_Range(code,days_start,days_end,discount_percentage)" +
                            " values('" + discountPenalty.code + "'," +
                            "" + discountPenalty.daySatrt + "," + discountPenalty.dayEnd + "," +
                            "" + discountPenalty.percentage + ")";
                   
                    conn.Open();
                    SqlCommand cmd1 = new SqlCommand(strQuery, conn);
                    retval = cmd1.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyDiscountPenalty(DiscountPenalty discountPenalty)
        {
            int retval = 0;
            string strQuery = "";
           
                if (discountPenalty.discountPenalty == "D")
                {
                    strQuery = "update mast_discount_maint set effective_date = '" + discountPenalty.effectiveDate.ToString("yyyy-MM-dd") + "',min_amount = " +
                        " " + discountPenalty.minAmount + ",max_amount =" + discountPenalty.maxAmount + " where customer_id = '" + discountPenalty.customerId + "'" +
                        " and discount_code = '" + discountPenalty.code + "'";
                }
                else
                {
                    strQuery = "update mast_panalty_maint set effective_date = '" + discountPenalty.effectiveDate.ToString("yyyy-MM-dd") + "',min_amount = " +
                        " " + discountPenalty.minAmount + ",max_amount =" + discountPenalty.maxAmount + " where customer_id = '" + discountPenalty.customerId + "'" +
                        " and panalty_code = '" + discountPenalty.code + "'";
                }
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(strQuery, conn);
                    retval = cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception exc)
                {
                    if (exc != null)
                    {
                        throw;
                    }
                }
                finally
                {
                    conn.Close();
                }

                try
                {
                    strQuery = "update conf_DiscountPenalty_Range set days_start = " + discountPenalty.daySatrt + ",days_end = " + discountPenalty.dayEnd + ", " +
                        " discount_percentage = " + discountPenalty.percentage + " where code ='" + discountPenalty.code + "' and Id = " + discountPenalty.autoIncrementId + "";
                       
                    conn.Open();
                    SqlCommand cmd1 = new SqlCommand(strQuery, conn);
                    retval = cmd1.ExecuteNonQuery();
                }
                catch (Exception exc)
                {
                    if (exc != null)
                    {
                        throw;
                    }
                }
                finally
                {
                    conn.Close();
                }

            return retval;
        }

        public int AddCusRemAccount(CustAccount account)
        {
            int retval = 0;
            //int maxCode;
            //string dicountPenaltyCode;
            string strQuery = "";

            // if (custRem == "C")
            {
                strQuery = "insert into mast_customer_account_maint(customer_id,account_number,account_name,bank_id)" +
                    " values('" + account.CustID + "','" + account.AccountNumber + "','" + account.AccountName + "','" + account.BankID + "')";
            }
            /*  else
              {
                  strQuery = "insert into mast_remitter_account_maint(remitter_id,account_number,account_name,bank_id)" +
                      " values('" + remitterId + "','" + accountNo + "','" + accountName + "','" + bankId + "')";
              }*/

            try
            {
                conn.Open();
                //string strQuery = "insert into mast_customer_account_maint(customer_id,account_number,account_name,bank_id)" +
                //    " values('" + account.CustID + "','" + account.AccountNumber + "','" + account.AccountName + "','" + account.BankID + "')";
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int UpdateCustomerAccountRecords(CustAccount account)
        {
            int retval = 0;
            string strQuery = "";
            //customer_id,account_number,
            strQuery = "Update mast_customer_account_maint set account_name = '" + account.AccountName + "'" +
                    " Where customer_id = '" + account.CustID + "' and account_number = '" + account.AccountNumber + "'";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int DeleteCustomerAccountRecords(String CustomerID, String AccountID)
        {
            int retval = 0;
            string strQuery = "";
            //customer_id,account_number,
            strQuery = "Delete From mast_customer_account_maint Where customer_id = '" + CustomerID + "' and account_number = '" + AccountID + "'";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int DeleteCustomer(String CustomerID)
        {
            int retval = 0;
            string strQuery = "";
            //customer_id,account_number,
            strQuery = "Delete From mast_customer_maint  Where customer_id = '" + CustomerID + "'";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int DeleteRemitter(String RemitterID, String CustomerID)
        {
            int retval = 0;
            string strQuery = "";
            strQuery = "Delete From mast_remitter_maint  Where remitter_id = '" + RemitterID + "' and customer_id = '" + CustomerID + "'";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int DeleteUser(String UserID)
        {
            int retval = 0;
            string strQuery = "";
            strQuery = "Delete From mast_user_maint  Where User_Id = '" + UserID + "'";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int DeleteRole(String RoleId)
        {
            int retval = 0;
            string strQuery = "";
            strQuery = "Delete From mast_role_maint  Where role_id = '" + RoleId + "'";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int AddInvoice(Invoice invc)
        {
            int retval = 0;
            string strQuery = "insert into pro_invoice(remitter_id,customer_id,invoice_number, date, amount," +
                "po_ref," +
                "invoic_status,is_partial" +
                ") values('" + invc.RemitterID + "','" + invc.CustomerID + "','" + invc.InvoiceNumber + "','" + invc.InvoiceDate.ToString("yyyy-MM-dd") + "'," +
                "" + invc.Amount + ",'" + invc.PORef + "'," +
                "'" + invc.InvoiceStatus + "'," + (invc.IsPartial ? 1 : 0) + ")";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int RegisterRole(UserRole role, string userID)
        {
            int retval = 0;
            string strQuery = "insert into mast_role_maint(Role_Id,Role_Desc,Role_Type,Is_Enabled," +
                "Entered_Id,Entered_Date,customer_id) values (" +
                "'" + role.RoleID + "','" + role.RoleDescription + "','" + role.RoleType + "'," + (role.IsEnabled ? 1 : 0).ToString() + "," +
                "'" + userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + role.CustomerID + "')";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int AddBankDetail(string bankID, string bankName, string ifsc, int isEnabled, string addedBy)
        {
            int retval = 0;
            string strQuery = "insert into mast_bank_maint(bank_id,bank_name, bank_ifsc, is_enabled," +
                "entered_id,entered_date" +
                ") values ('" + bankID + "','" + bankName + "','" + ifsc + "'," + isEnabled + ",'" + addedBy + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int addRoleMenuMapping(string roleId, string menuId, int isEnabled, string addedBy, string CustomerID)
        {
            int retval = 0;
            string strQuery = "insert into mast_role_menu_maint (role_id,menu_id,is_enabled,entered_id,entered_date,customer_id)" +
                "values (" +
                "'" + roleId + "','" + menuId + "'," + isEnabled + "," +
                "'" + addedBy + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + CustomerID + "')";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int removeRoleMenuMapping(string roleId, string menuId)
        {
            int retval = 0;
            string strQuery = "delete from mast_role_menu_maint where role_id = '" + roleId + "' and menu_id = '" + menuId + "'";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyUser(User user, string userID)
        {
            int retval = 0;
            string strQuery = "update mast_user_maint set User_Type  = '" + user.UserType + "',Remitter_Id =  '" + user.RemitterID + "'," +
                   "User_Name =  '" + user.UserName + "',Role_Id =  '" + user.RoleID + "',Email_Id =  '" + user.EmailAddress + "',Mobile_No =  '" + user.MobileNumber + "',Is_Enabled =  '" + (user.IsEnabled ? 1 : 0) + "'," +
                //"User_Name =  '" + user.UserName + "',User_Password =  '" + Utils.EncryptValue(user.Password) + "' ,Role_Id =  '" + user.RoleID + "',Email_Id =  '" + user.EmailAddress + "',Mobile_No =  '" + user.MobileNumber + "',Is_Enabled =  '" + (user.IsEnabled ? 1 : 0) + "'," +
                "Is_Locked =  '" + (user.IsLocked ? 1 : 0) + "',Is_Active =  '" + (user.IsActive ? 1 : 0) + "',Login_Attempt =  '0',LatLogin_DateTime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',Password_ExpiryDate =  '" + System.DateTime.Now.AddDays(90).ToString("yyyy-MM-dd") + "'," +
                "ModBy_UserId =  '" + userID + "',ModBy_Date =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',CurrentLogin_Datetime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',Is_DomainUser =  '" + (user.IsDomainUser ? 1 : 0) + "' where  User_Id = '" + user.UserID + "' and Customer_Id = '" + user.CustomerID + "'";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }


















































































        public int setForcePwdChange(User user)
        {
            int retval = 0;

            try
            {
                string tempPwd = Utils.GenerateRandomPassword();
                conn.Open();
                SqlCommand cmd = new SqlCommand("update mast_user_maint set Is_ForcedPwdChange = " + (user.IsForcedPasswordChange ? 1 : 0).ToString() + ", User_Password = '" + Utils.EncryptValue(tempPwd) + "' where  User_Id = '" + user.UserID + "' and Customer_ID = '" + user.CustomerID + "'", conn);
                retval = cmd.ExecuteNonQuery();
                Utils.sendEmail("Hi!<br /><br />Your new password for 4Circlesolutions.com is: <b>" + tempPwd + "</b>.<br /><br />you would be required to change the same upon first sign-in", "4Circlesolutions.com Password Reset", user.EmailAddress);
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }
        public int changePassword(User user, string newPwd)
        {
            int retval = 0;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("update mast_user_maint set User_Password ='" + Utils.EncryptValue(newPwd) + "', Is_ForcedPwdChange = 0 where  User_Id = '" + user.UserID + "' AND Customer_ID =  '" + (string.IsNullOrEmpty(user.CustomerID) ? "SYSTEM" : user.CustomerID) + "'", conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyCustomer(Customer cust, string userID)
        {
            int retval = 0;
            string strQuery = "update mast_customer_maint set customer_name  = '" + cust.CustomerName + "',customer_address1 =  '" + cust.Address1 + "',customer_address2 = '" + cust.Address2 + "'," +
                //"customer_city =  '" + cust.City + "',customer_state =  '" + cust.State + "' ,customer_country =  '" + cust.Country + "',customer_zipcode =  '" + cust.ZipCode + "',customer_telno =  '" + cust.TelephoneNumber + "',customer_emailid =  '" + cust.EmailAddress + "',is_enabled =  '" + (cust.IsEnabled ? 1 : 0) + "',is_active =  '" + (cust.IsActive ? 1 : 0) + "'," +
                "customer_city =  '" + cust.City + "',customer_state =  '" + cust.State + "' ,customer_country =  '" + cust.Country + "'," +
                "customer_zipcode =  '" + cust.ZipCode + "',customer_telno =  '" + cust.TelephoneNumber + "',customer_emailid =  '" + cust.EmailAddress + "',is_enabled =  '" + (cust.IsEnabled ? 1 : 0) + "'," +
                "modby_id =  '" + userID + "',modby_datetime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' where  customer_id = '" + cust.CustomerID + "'";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyBankDetail(string bankID, string bankName, string ifsc, int isEnabled, string addedBy)
        {
            int retval = 0;
            string strQuery = "update mast_bank_maint set bank_name  = '" + bankName + "',bank_ifsc =  '" + ifsc + "',is_enabled = " + isEnabled.ToString() + "," +
                "modified_id =  '" + addedBy + "',modified_date =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' where  bank_id = '" + bankID + "'";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }




        public int ModifyRemitter(Remitter remitter, string userID)
        {
            int retval = 0;
            string strQuery = "update mast_remitter_maint set remitter_name  = '" + remitter.RemitterName + "',address1 =  '" + remitter.Address1 + "',address2 = '" + remitter.Address2 + "',address3 = '" + remitter.Address3 + "'," +
                //"city =  '" + remitter.City + "',state =  '" + remitter.State + "' ,country =  '" + remitter.Country + "',zipcode =  '" + remitter.ZipCode + "',tel_no =  '" + remitter.TelephoneNumber + "',email_id =  '" + remitter.EmailAddress + "',is_enable =  '" + (remitter.IsEnabled ? 1 : 0) + "',is_active=  '" + (remitter.IsActive ? 1 : 0) + "'," +
                "city =  '" + remitter.City + "',state =  '" + remitter.State + "' ,country =  '" + remitter.Country + "',zipcode =  '" + remitter.ZipCode + "'," + 
                "tel_no =  '" + remitter.TelephoneNumber + "',email_id =  '" + remitter.EmailAddress + "',is_enable =  '" + (remitter.IsEnabled ? 1 : 0) + "'," +
                "discount_code='" + remitter.DiscountCode + "',penalty_code='" + remitter.PenaltyCode + "'," +
                "modby_id =  '" + userID + "',modby_datetime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' where  remitter_id = '" + remitter.RemitterID + "' and customer_id = '" + remitter.CustomerID + "'";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyRole(UserRole role, string userID)
        {
            int retval = 0;
            string strQuery = "update mast_role_maint set Role_Desc  = '" + role.RoleDescription + "',Role_Type =  '" + role.RoleType + "',Is_Enabled = '" + (role.IsEnabled ? 1 : 0) + "'," +
                "modified_id =  '" + userID + "',modified_date =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' where  Role_Id = '" + role.RoleID + "' and customer_id = '" + role.CustomerID + "'";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public bool ValidateUser(User user)
        {
            bool retval = false;

            try
            {
                SqlCommand cmd = new SqlCommand("SELECT User_Id,User_Name,User_Type,r.Remitter_Id,u.Customer_Id,u.Role_Id AS Role_ID,Customer_Name, Remitter_Name, Is_ForcedPwdChange FROM mast_user_maint u LEFT OUTER JOIN mast_customer_maint c on u.Customer_ID = c.Customer_ID LEFT OUTER JOIN mast_Remitter_maint r on u.Remitter_ID = r.Remitter_ID " +
                    "WHERE User_ID = @uid and User_Password = @pwd and u.customer_id = @custid and (AuthBy_UserId is not null or AuthBy_UserId  <> '')" +
                    "and (AuthBy_Date is not null or AuthBy_Date  <> '') and u.Is_Enabled = 1 and Is_Locked = 0 and u.Is_Active = 1 " +
                    "AND u.Customer_ID " + (user.CustomerID == "SYSTEM" ? "= 'SYSTEM'" : "IN ( select Customer_Id from mast_customer_maint where customer_id = @custid and is_enabled=1 and  is_active = 1)"), conn);
                cmd.Parameters.Add("@uid", SqlDbType.VarChar, 10);
                cmd.Parameters["@uid"].Value = user.UserID;
                cmd.Parameters.Add("@pwd", SqlDbType.VarChar, 50);
                cmd.Parameters["@pwd"].Value = Utils.EncryptValue(user.Password);
                cmd.Parameters.Add("@custid", SqlDbType.VarChar, 10);
                cmd.Parameters["@custid"].Value = user.CustomerID;

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        user = new User
                        {
                            UserID = reader.GetString(0),
                            UserName = reader.GetString(1),
                            UserType = reader.GetString(2),
                            RemitterID = (reader.IsDBNull(3) ? string.Empty : reader.GetString(3)),
                            CustomerID = reader.GetString(4),
                            RoleID = reader.GetString(5),
                            CustomerName = (reader.IsDBNull(6) ? string.Empty : reader.GetString(6)),
                            RemitterName = (reader.IsDBNull(7) ? string.Empty : reader.GetString(7)),
                            IsForcedPasswordChange = (reader.IsDBNull(8) ? false : Convert.ToBoolean(reader.GetValue(8)))
                        };

                        HttpContext.Current.Session["CurrentUserVO"] = user;
                    }

                    retval = true;
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return retval;
        }

        public User GetUserSpecificDetails(string userID, string custID)
        {
            User user = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT User_Name, Customer_ID, Email_id FROM mast_user_maint WHERE User_ID = @userid and customer_id = @custid", conn);
                //SqlCommand cmd = new SqlCommand("SELECT User_Name, Customer_ID, Email_Id,Remitter_Id FROM mast_user_maint WHERE User_ID = ? and (AuthBy_UserId is not null or AuthBy_UserId  <> '') and (AuthBy_Date is not null or AuthBy_Date  <> '') and customer_id = ?", conn);
                cmd.Parameters.Add("@userid", SqlDbType.VarChar, 10);
                cmd.Parameters[0].Value = userID;
                cmd.Parameters.Add("@custid", SqlDbType.VarChar, 10);
                cmd.Parameters[1].Value = custID;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        user = new User
                        {
                            UserName = reader.IsDBNull(0) ? string.Empty : Convert.ToString(reader.GetString(0)),
                            CustomerID = reader.IsDBNull(1) ? string.Empty : Convert.ToString(reader.GetString(1)),
                            EmailAddress = reader.IsDBNull(2) ? string.Empty : Convert.ToString(reader.GetString(2)),
                            UserID=userID
                        };
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }
            return user;
        }
        public DataTable populateMenu(string roleId, string CustomerID)
        {
            DataSet ds = new DataSet();

            try
            {
                SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM conf_menu_maint where menu_id in (SELECT menu_id FROM mast_role_menu_maint where role_id = '" + roleId + "' AND customer_id='"+CustomerID+"') OR menu_type = 'CO' ORDER BY parent_id, menu_order", conn);

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                adp.Fill(ds);
                adp = null;
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return (ds.Tables.Count >= 0) ? ds.Tables[0] : null;
        }

        public int AddRemitter(Remitter remitter, string userID)
        {
            int retval = 0;
            string strQuery = "insert into mast_remitter_maint(remitter_id,remitter_name,customer_id, address1, address2,address3," +
                "city,state,country,zipcode,tel_no,email_id,is_enable,is_active," +
                //"city,state,country,zipcode,tel_no,email_id,is_enable," +
                "discount_code, penalty_code," +
                "addby_id,addby_datetime" +
                ") values ('" + remitter.RemitterID + "','" + remitter.RemitterName + "','" + remitter.CustomerID + "','" + remitter.Address1 + "','" + remitter.Address2 + "'," +
                "'" + remitter.Address3 + "','" + remitter.City + "','" + remitter.State + "','" + remitter.Country + "'," +
                "'" + remitter.ZipCode + "','" + remitter.TelephoneNumber + "','" + remitter.EmailAddress + "'," +
                //"" + (remitter.IsEnabled ? 1 : 0) + "," + (remitter.IsActive ? 1 : 0) + ",'" + remitter.DiscountCode + "','" + remitter.PenaltyCode + "'," +
                 "" + (remitter.IsEnabled ? 1 : 0) + ",'1','" + remitter.DiscountCode + "','" + remitter.PenaltyCode + "'," +
                "'" + userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public DataTable GetPendingInvoiceCountAndAmountForChart(string CustomerId, string UserId)
        {
            DataSet ds = new DataSet();
            try
            {
                string strSQL = "SELECT 'Pending Reconciliation' as PendingInvoice, Count(*) as InvoiceCount, IsNull(round((SUM(amount)/100000),2),0) as InvoiceAmount FROM pro_invoice WHERE customer_id = '" + CustomerId + "' AND invoic_status IN ('N', 'U', 'P', 'R')";
                strSQL += " UNION ";
                strSQL += "SELECT 'Pending Authorization' as PendingInvoice, Count(*) as InvoiceCount, IsNull(round((SUM(amount)/100000),2),0) as InvoiceAmount FROM pro_invoice WHERE customer_id = '" + CustomerId + "' AND invoic_status IN ('M', 'C')";
                strSQL += " UNION ";
                //remove remitter check from below query to fetch invoices count/amount sent to remitter for reconciliation
                strSQL += "SELECT 'Pending Remitter Matching' as PendingInvoice, Count(*) as InvoiceCount, IsNull(round((SUM(amount)/100000),2),0) as InvoiceAmount FROM pro_invoice WHERE customer_id = '" + CustomerId + "' AND invoic_status = 'D'";


                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "LineChartData");
                
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public int GetUnmatchedInvoiceCount(string CustomerID, int minDays, int maxDays)
        {
            int ctr = 0;

            try
            {

                string strSQL = "SELECT COUNT(*) FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND invoic_status IN ('N', 'U', 'P', 'R')";
                if (minDays != 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) between " + minDays.ToString() + " and " + maxDays.ToString() + "";
                }
                else if (minDays != 0 && maxDays == 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) >= " + minDays.ToString();
                }
                else if (minDays == 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) <= " + maxDays.ToString() + "";
                }
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                ctr = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return ctr;
        }

        public int GetUnmatchedInvoiceCountForRemitter(string CustomerID, string UserID, int minDays, int maxDays)
        {
            int ctr = 0;

            try
            {
                //remove remitter check from below query to fetch invoices count/amount sent to remitter for reconciliation
                string strSQL = "SELECT COUNT(*) FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND invoic_status = 'D'";
                if (minDays != 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) between " + minDays.ToString() + " and " + maxDays.ToString() + "";
                }
                else if (minDays != 0 && maxDays == 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) >= " + minDays.ToString();
                }
                else if (minDays == 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) <= " + maxDays.ToString() + "";
                }
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                ctr = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return ctr;
        }

        public int GetUnauthorizedInvoiceCount(string CustomerID, int minDays, int maxDays)
        {
            int ctr = 0;
            try
            {
                string strSQL = "SELECT COUNT(*) FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND invoic_status IN ('M', 'C')";
                if (minDays != 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) between " + minDays.ToString() + " and " + maxDays.ToString() + "";
                }
                else if (minDays != 0 && maxDays == 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) >= " + minDays.ToString();
                }
                else if (minDays == 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF(day, '" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) <= " + maxDays.ToString() + "";
                }
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                ctr = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return ctr;
        }
        public int DeleteInvoice(string invoiceID)
        {
            try
            {
                string Query = "";

                Query = "DELETE from pro_invoice where invoice_number='"+invoiceID+"'";
                conn.Open();

               SqlCommand cmd = new SqlCommand(Query, conn);
               return cmd.ExecuteNonQuery();
               
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }
            return -1;
        }
        public int DeleteStatement(string StatementID)
        {
            try
            {
                string Query = "";

                Query = "DELETE from pro_statement where statement_unique_id='" + StatementID + "'";
                conn.Open();

                SqlCommand cmd = new SqlCommand(Query, conn);
                return cmd.ExecuteNonQuery();

            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }
            return -1;
        }
        public List<Invoice> GetInvoicesForAmendment(string CustomerID, string RemitterId)
        {
            List<Invoice> invoices = null;

            try
            {
                string Query = "SELECT invoice_unique_id, remitter_id, invoice_number, date, po_ref, amount, invoic_status, is_partial FROM pro_invoice WHERE remitter_id='" + RemitterId + "' AND customer_id = '" + CustomerID + "' AND invoic_status ='N'";
                conn.Open();

                SqlCommand cmd = new SqlCommand(Query, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    invoices = new List<Invoice>();
                    while (reader.Read())
                    {
                        Invoice invoice = new Invoice
                        {
                            InvoiceID = reader.IsDBNull(0) ? "0" : Convert.ToString(reader.GetInt32(0)),
                            RemitterID = reader.IsDBNull(1) ? string.Empty : reader.GetString(1),
                           // RemitterName = reader.GetString(2),
                            InvoiceNumber = reader.IsDBNull(2) ? string.Empty : reader.GetString(2),
                            InvoiceDate = reader.GetDateTime(3),
                            //Currency = reader.IsDBNull(4) ? string.Empty : reader.GetString(4),
                            PORef = reader.IsDBNull(4) ? string.Empty : reader.GetString(4),
                            Amount = reader.IsDBNull(5) ? 0.00 : Convert.ToDouble(reader.GetValue(5)),
                            InvoiceStatus = reader.IsDBNull(6) ? string.Empty : reader.GetString(6),
                            IsPartial = reader.IsDBNull(7) ? false : Convert.ToBoolean(reader.GetValue(7))
                           // ReconciledAmount = (reader.IsDBNull(10) ? 0 : reader.GetDouble(10)),
                           // AmountToReconcile = (reader.IsDBNull(10) ? reader.GetDouble(7) : reader.GetDouble(7) - reader.GetDouble(10))
                        };
                        invoices.Add(invoice);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return invoices;
        }



        public List<Invoice> GetUnmatchedInvoices(string CustomerID, string RemitterID, int MinDays, int MaxDays)
        {
            List<Invoice> invoices = null;

            try
            {
                StringBuilder strSQL = new StringBuilder("SELECT invoice_unique_id, r.remitter_id, r.remitter_name, invoice_number, date,  po_ref, amount, invoic_status, is_partial, reconciled_amount " +
                    "FROM pro_invoice i RIGHT OUTER JOIN mast_remitter_maint r on r.remitter_id = i.remitter_id " +
                    "WHERE i.customer_id = '" + CustomerID + "'" +
                    (RemitterID != "0" ? " AND r.remitter_id = '" + RemitterID + "'" : ""));
                if (MinDays > 0 && MaxDays > 0)
                {
                    strSQL.Append(" AND DATEDIFF(D, date, GETDATE()) BETWEEN " + MinDays.ToString() + " AND " + MaxDays.ToString());
                }
                else if (MinDays > 0)
                {
                    strSQL.Append(" AND DATEDIFF(D, date, GETDATE()) > " + MinDays.ToString());
                }
                else if (MaxDays > 0)
                {
                    strSQL.Append(" AND DATEDIFF(D, date, GETDATE()) < " + MaxDays.ToString());
                }
                strSQL.Append(" AND (invoic_status IN ('N', 'U', 'P', 'R') OR is_partial = 1)");
                SqlCommand cmd = new SqlCommand(strSQL.ToString(), conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    invoices = new List<Invoice>();
                    while (reader.Read())
                    {
                        Invoice invoice = new Invoice
                        {
                            InvoiceID = Convert.ToString(reader.GetInt32(0)),
                            RemitterID = reader.GetString(1),
                            RemitterName = reader.GetString(2),
                            InvoiceNumber = reader.GetString(3),
                            InvoiceDate = reader.GetDateTime(4),
                            PORef = reader.GetString(5),
                            Amount = reader.GetDouble(6),
                            InvoiceStatus = reader.GetString(7),
                            IsPartial = Convert.ToBoolean(reader.GetValue(8)),
                            ReconciledAmount = (reader.IsDBNull(9) ? 0 : reader.GetDouble(9)),
                            AmountToReconcile = ((reader.GetDouble(6) > 0 ? 1 : -1) * reader.GetDouble(6)) - (reader.IsDBNull(9) ? 0 : reader.GetDouble(9))
                        };
                        invoices.Add(invoice);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return invoices;
        }

        public List<Invoice> GetUnmatchedInvoicesForRemitter(string CustomerID, string UserID)
        {
            List<Invoice> invoices = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT invoice_unique_id, remitter_id, invoice_number, date, po_ref, amount, invoic_status, is_partial, reconciled_amount FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND remitter_id = (SELECT remitter_id FROM mast_user_maint WHERE user_id='" + UserID + "' and customer_id = '" + CustomerID + "' and (AuthBy_UserId is not null or AuthBy_UserId  <> '') and (AuthBy_Date is not null or AuthBy_Date  <> '')) AND (invoic_status = 'D' OR is_partial = 1)", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    invoices = new List<Invoice>();
                    while (reader.Read())
                    {
                        Invoice invoice = new Invoice
                        {
                        InvoiceID = reader.GetInt32(0).ToString(),
                        RemitterID = reader.GetString(1),
                        InvoiceNumber = reader.GetString(2),
                        InvoiceDate = reader.GetDateTime(3),
                        //Currency = reader.GetString(4),
                        PORef = reader.GetString(4),
                        Amount = reader.GetDouble(5),
                        InvoiceStatus = reader.GetString(6),
                        IsPartial = (reader.GetByte(7) == 1),
                        ReconciledAmount = reader.GetDouble(8),
                        //AmountToReconcile = (reader.IsDBNull(9) ? reader.GetDouble(6) : reader.GetDouble(6) - reader.GetDouble(9)),
                        };

                        invoices.Add(invoice);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return invoices;
        }

        public List<Statement> GetUnmatchedStatements(string CustomerID)
        {
            List<Statement> statements = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT statement_unique_id, bank_id, value_date, ref_no_or_cheque_no, narration1, amount, reconciled_amount,statement_date,posting_date, statement_status, is_partial FROM pro_statement WHERE customer_id = '" + CustomerID + "' AND (statement_status IN ('N', 'U', 'P', 'R') OR is_partial = 1)", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    statements = new List<Statement>();
                    while (reader.Read())
                    {
                        Statement statement = new Statement
                        {
                            StatementID = Convert.ToString(reader.GetInt32(0)),
                            BankID = reader.GetString(1),
                            valueDate = reader.GetDateTime(2),
                            referenceNumber = reader.GetString(3),
                            narration1 = reader.GetString(4),
                            statementDate = reader.GetDateTime(7),
                            postingDate = reader.GetDateTime(8),
                            amount = reader.GetDouble(5),
                            ReconciledAmount = (reader.IsDBNull(6) ? 0 : reader.GetDouble(6)),
                            AmountToReconcile = reader.GetDouble(5) - (reader.IsDBNull(6) ? 0 : reader.GetDouble(6)),
                            Status = reader.GetString(9),
                            IsPartial = Convert.ToBoolean(reader.GetValue(10))
                        };
                        statements.Add(statement);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return statements;
        }


        public List<Statement> GetUnmatchedStatementsForRemitter(string CustomerID,string RemitterID,string Ref)
        {
            List<Statement> statements = null;

            string sQuery = "";

            if (Ref != null)
            {
                sQuery = "SELECT s.statement_unique_id, s.bank_id, s.value_date, s.ref_no_or_cheque_no, s.narration1, s.amount, s.reconciled_amount FROM pro_statement s join"
                    + " mast_remitter_account_maint acc on (s.ref_no_or_cheque_no like CONCAT('%'," + Ref + ", '%') or s.narration1 like CONCAT('%'," + Ref + ", '%'))"
                    + " WHERE s.customer_id = '"
                    + CustomerID + "' AND (s.statement_status IN ('N', 'U', 'P', 'R') OR s.is_partial = 1) AND acc.remitter_id = '"
                    + RemitterID + "'";
            }
            else
            {
                sQuery = "SELECT s.statement_unique_id, s.bank_id, s.value_date, s.ref_no_or_cheque_no, s.narration1, s.amount, s.reconciled_amount FROM pro_statement s join"
                    + " mast_remitter_account_maint acc on (s.ref_no_or_cheque_no like CONCAT('%', acc.account_number, '%') or s.narration1 like CONCAT('%', acc.account_number, '%'))"
                    + " WHERE s.customer_id = '"
                    + CustomerID + "' AND (s.statement_status IN ('N', 'U', 'P', 'R') OR s.is_partial = 1) AND acc.remitter_id = '"
                    + RemitterID + "'";
            }
                
                

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sQuery, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    statements = new List<Statement>();
                    while (reader.Read())
                    {
                        Statement statement = new Statement
                        {
                            StatementID = Convert.ToString(reader.GetInt32(0)),
                            BankID = reader.GetString(1),
                            valueDate = reader.GetDateTime(2),
                            referenceNumber = reader.GetString(3),
                            narration1 = reader.GetString(4),
                            amount = reader.GetDouble(5),
                            ReconciledAmount = (reader.IsDBNull(6) ? 0 : reader.GetDouble(6)),
                            AmountToReconcile = (reader.IsDBNull(6) ? reader.GetDouble(5) : reader.GetDouble(5) - reader.GetDouble(6))
                        };
                        statements.Add(statement);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return statements;
        }




        public List<MatchedInvoice> GetUnauthorizedInvoices(string CustomerID)
        {
            List<MatchedInvoice> invoices = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT mtch.invoice_unique_id, mtch.statement_unique_id, " +
                    "inv.invoice_number, inv.date, inv.remitter_id, rem.remitter_name, inv.amount as inv_amount, " +
                    "stmt.ref_no_or_cheque_no, stmt.value_date, stmt.narration1, stmt.amount as stmt_amount " +
                    "FROM pro_matching mtch " +
                    "JOIN pro_invoice inv ON mtch.invoice_unique_id = inv.invoice_unique_id " +
                    "JOIN pro_statement stmt ON mtch.statement_unique_id = stmt.statement_unique_id " +
                    "JOIN mast_remitter_maint rem on rem.remitter_id = inv.remitter_id " +
                    "WHERE inv.customer_id = @custid AND stmt.customer_id = @custid " +
                    "AND invoic_status IN ('M', 'C') and statement_status IN ('M', 'C')", conn);
                cmd.Parameters.Add("@custid", SqlDbType.VarChar, 10);
                cmd.Parameters["@custid"].Value = CustomerID;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    invoices = new List<MatchedInvoice>();
                    while (reader.Read())
                    {
                        MatchedInvoice invoice = new MatchedInvoice
                        {
                            InvoiceUniqueID = reader.GetDecimal(0).ToString(),
                            StatementUniqueID = reader.GetDecimal(1).ToString(),
                            InvoiceNumber = reader.GetString(2),
                            InvoiceDate = reader.GetDateTime(3),
                            RemitterID = reader.GetString(4),
                            RemitterName = reader.GetString(5),
                            InvoiceAmount = Convert.ToDouble(reader.GetValue(6)),
                            ReferenceNumber = reader.GetString(7),
                            ValueDate = reader.GetDateTime(8),
                            Narration1 = reader.GetString(9),
                            StatementAmount = Convert.ToDouble(reader.GetValue(10))
                        };
                        invoices.Add(invoice);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return invoices;
        }

        public List<Statement> GetStatementDetailsForRemitter(string CustomerID, string PaymentReferenceNumber)
        {
            List<Statement> statements = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT statement_unique_id, amount as statement_amount FROM pro_statement WHERE customer_id = '" + CustomerID + "' AND ref_no_or_cheque_no = '" + PaymentReferenceNumber + "' AND statement_status IN ('N', 'U', 'P', 'R')", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    statements = new List<Statement>();
                    while (reader.Read())
                    {
                        Statement statement = new Statement
                        {
                            StatementID = reader.GetInt32(0).ToString(),
                            amount = reader.GetDouble(1),
                            referenceNumber=PaymentReferenceNumber,
                            CustomerID=CustomerID,
                        };
                        statements.Add(statement);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return statements;
        }

        public void MatchInvoices(List<MatchedInvoice> matchList, string matchType, string custID, string userID)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                foreach (MatchedInvoice invc in matchList)
                {
                    string strQuery = "if not exists (select (1) from pro_matching where Customer_ID = '" + custID + "' AND Invoice_Unique_ID = " + invc.InvoiceUniqueID + " AND Statement_Unique_ID = " + invc.StatementUniqueID + ") " +
                        "insert into pro_matching(customer_id, invoice_unique_id, statement_unique_id, invoice_amount, statement_amount, match_by, match_date, auth_by, auth_date, remitter_user, remitter_date, match_type) values (" +
                        "'" + custID + "'," + invc.InvoiceUniqueID + "," + invc.StatementUniqueID + "," +
                        invc.InvoiceAmountToReconcile + "," + invc.StatementAmountToReconcile + ",'" +
                        userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + matchType + "') " +
                        "else " +
                        "update pro_matching set invoice_amount = invoice_amount + " + invc.InvoiceAmountToReconcile + ", statement_amount = statement_amount + " + invc.StatementAmountToReconcile + ", " +
                        "match_by = '" + userID + "', match_date = '" + System.DateTime.Now.ToString("yyyy-MM-dd") +
                        "', auth_by = '', auth_date = '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "', remitter_user = '', remitter_date = '" + System.DateTime.Now.ToString("yyyy-MM-dd") +
                        "', match_type = '" + matchType + "' where Customer_ID = '" + custID + "' AND Invoice_Unique_ID = " + invc.InvoiceUniqueID + " AND Statement_Unique_ID = " + invc.StatementUniqueID;

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_invoice set " +
                        "invoic_status='" + (invc.IsPartialInvoiceMatch ? "P" : "M") + 
                        "',reconciled_amount=case when reconciled_amount is null then " + invc.InvoiceAmountToReconcile + " when reconciled_amount is not null then (SELECT SUM(invoice_amount) from pro_matching m where m.customer_id = '" + custID + "' AND m.invoice_unique_id = '" + invc.InvoiceUniqueID + "') end " + 
                        ",is_partial= " + (invc.IsPartialInvoiceMatch ? 1 : 0) + 
                            " where customer_id='" + custID + "' and invoice_number='" + invc.InvoiceNumber + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    //if statement amount is +ve then statement else credit note
                    if (invc.StatementAmount > 0)
                    {
                        strQuery = "update pro_statement set " +
                                "statement_status='" + (invc.IsPartialStatementMatch ? "P" : "M") +
                                "',reconciled_amount=case when reconciled_amount is null then " + invc.StatementAmountToReconcile + " when reconciled_amount is not null then (SELECT SUM(statement_amount) from pro_matching m where m.customer_id = '" + custID + "' AND m.statement_unique_id = '" + invc.StatementUniqueID + "') end " + 
                                ",is_partial= " + (invc.IsPartialStatementMatch ? 1 : 0) + 
                                " where customer_id='" + custID + "' and ref_no_or_cheque_no='" + invc.ReferenceNumber + "'";
                    }
                    else
                    {
                        strQuery = "update pro_invoice set " +
                            "invoic_status='" + (invc.IsPartialStatementMatch ? "P" : "M") +
                            "',reconciled_amount=case when reconciled_amount is null then " + invc.StatementAmountToReconcile + " when reconciled_amount is not null then (SELECT SUM(statement_amount) from pro_matching m where m.customer_id = '" + custID + "' AND statement_unique_id = '" + invc.StatementUniqueID + "') end " + 
                            ",is_partial= " + (invc.IsPartialStatementMatch ? 1 : 0) + 
                                " where customer_id='" + custID + "' and invoice_number='" + invc.ReferenceNumber + "'";
                    }

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

        }

        public void MatchInvoicesForRemitter(List<MatchedInvoice> matchList, string custID, string userID)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                foreach (MatchedInvoice invc in matchList)
                {
                    string strQuery = "insert into pro_matching(customer_id, invoice_unique_id, statement_unique_id,"
                        +" invoice_amount, statement_amount, match_by, match_date, auth_by, auth_date, remitter_user, remitter_date, match_type) values (" +
                        "'" + custID + "'," + ((invc.InvoiceUniqueID != null || invc.InvoiceUniqueID.Equals(string.Empty)) ? Convert.ToDouble(invc.InvoiceUniqueID) : 0.0) 
                        + "," + ((invc.InvoiceUniqueID != null || invc.StatementUniqueID.Equals(string.Empty)) ? Convert.ToDouble(invc.StatementUniqueID) : 0.0) + "," +
                        invc.InvoiceAmount + "," + invc.StatementAmount + "," +
                        "' ','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',' ','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','manual')";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_invoice set " +
                            "invoic_status='C',is_partial=0" +
                            "where customer_id='" + custID + "' and invoice_number='" + invc.InvoiceNumber + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                   /* strQuery = "update pro_statement set " +
                            "statement_status='C',is_partial=0" +
                            "where customer_id='" + custID + "' and ref_no_or_cheque_no='" + invc.ReferenceNumber + "'";*/


                    strQuery = "update pro_statement set " +
                            "statement_status='C',is_partial=0" +
                            "where customer_id='" + custID + "' and statement_unique_id='" + invc.StatementUniqueID + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

        }

        public void SendInvoicesToRemitter(List<Invoice> invoiceList)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                foreach (Invoice invc in invoiceList)
                {
                    string strQuery = "update pro_invoice set " +
                            "invoic_status='D',is_partial= 0 " +  //,is_partial= false " +
                            "where customer_id='" + invc.CustomerID + "' and invoice_number='" + invc.InvoiceNumber + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

        }

        public void AuthorizeInvoices(List<MatchedInvoice> matchList, string custID, string userID)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                foreach (MatchedInvoice invc in matchList)
                {
                    string strQuery = "update pro_matching set " +
                        "auth_by='" + userID + "', auth_date='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                        "where Customer_ID='" + custID + "' and invoice_unique_id=" + invc.InvoiceUniqueID + " and statement_unique_id=" + invc.StatementUniqueID;
                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_invoice set " +
                            "invoic_status='A',is_partial=0 " +
                            "where customer_id='" + custID + "' and invoice_number='" + invc.InvoiceNumber + "'";
                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_statement set " +
                            "statement_status='A',is_partial=0 " +
                            "where customer_id='" + custID + "' and ref_no_or_cheque_no='" + invc.ReferenceNumber + "'";
                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }
        }

        public void RejectInvoices(List<MatchedInvoice> matchList, string custID, string userID)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                foreach (MatchedInvoice invc in matchList)
                {
                    string strQuery = "update pro_matching set " +
                        "auth_by='" + userID + "', auth_date='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                        "where Customer_ID='" + custID + "' and invoice_unique_id=" + invc.InvoiceUniqueID + " and statement_unique_id=" + invc.StatementUniqueID;
                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_invoice set " +
                            "invoic_status='R',is_partial=0" +
                            "where customer_id='" + custID + "' and invoice_number='" + invc.InvoiceNumber + "'";
                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_statement set " +
                            "statement_status='R',is_partial=0" +
                            "where customer_id='" + custID + "' and ref_no_or_cheque_no='" + invc.ReferenceNumber + "'";
                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }
        }

        public List<User> GetUserDetailsByUserId(string UserID, string customerId)
        {
            List<User> lstUser = null;
            User user = null;

            try
            {
                SqlCommand cmd;
                conn.Open();
                if (string.IsNullOrEmpty(UserID))
                    cmd = new SqlCommand("SELECT user_id,User_Type,Remitter_Id,Customer_Id,user_name,Role_Id,Email_Id,Mobile_No,Is_Enabled,Is_Locked,Is_Active FROM mast_user_maint WHERE user_id like '" + UserID + "%' and Customer_Id in ( select Customer_Id from mast_customer_maint where customer_id = '" + customerId + "' and is_enabled=1 and  is_active = 1)", conn);
                else
                    cmd = new SqlCommand("SELECT user_id,User_Type,Remitter_Id,Customer_Id,user_name,Role_Id,Email_Id,Mobile_No,Is_Enabled,Is_Locked,Is_Active FROM mast_user_maint WHERE user_id = '" + UserID + "'  and Customer_Id in ( select Customer_Id from mast_customer_maint where customer_id = '" + customerId + "' and is_enabled=1 and  is_active = 1)", conn);

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    lstUser = new List<User>();
                    while (reader.Read())
                    {

                        user = new User
                        {
                            UserID = reader.IsDBNull(0) ? String.Empty : reader.GetString(0),
                            UserType = reader.IsDBNull(1) ? String.Empty : reader.GetString(1),
                            RemitterID = reader.IsDBNull(2) ? String.Empty : reader.GetString(2),
                            CustomerID = reader.IsDBNull(3) ? String.Empty : reader.GetString(3),
                            UserName = reader.IsDBNull(4) ? String.Empty : reader.GetString(4),
                            RoleID = reader.IsDBNull(5) ? String.Empty : reader.GetString(5),
                            EmailAddress = reader.IsDBNull(6) ? String.Empty : reader.GetString(6),
                            MobileNumber = reader.IsDBNull(7) ? String.Empty : reader.GetString(7),
                            IsEnabled = reader.IsDBNull(8) ? false : Convert.ToBoolean(reader.GetValue(8)),
                            IsLocked = reader.IsDBNull(9) ? false : Convert.ToBoolean(reader.GetValue(9)),
                            IsActive = reader.IsDBNull(10) ? false : Convert.ToBoolean(reader.GetValue(10))
                        };
                        lstUser.Add(user);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return lstUser;
        }

        public List<Customer> GetCustomerDetailsByCustomerId(string CustomerID)
        {
            List<Customer> lstCustomer = null;
            Customer cust = null;

            try
            {
                conn.Open();
                SqlCommand cmd;
                if (string.IsNullOrEmpty(CustomerID))
                    cmd = new SqlCommand("select  customer_id,customer_name, customer_address1, customer_address2,customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled,is_active from mast_customer_maint where customer_id like '" + CustomerID + "%'", conn);
                //cmd = new SqlCommand("select  customer_id,customer_name, customer_address1, customer_address2,customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled,is_active from mast_customer_maint where customer_id like '" + CustomerID + "%' and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                else
                    cmd = new SqlCommand("select  customer_id,customer_name, customer_address1, customer_address2,customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled,is_active from mast_customer_maint where customer_id = '" + CustomerID + "'", conn);
                //cmd = new SqlCommand("select  customer_id,customer_name, customer_address1, customer_address2,customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled,is_active from mast_customer_maint where customer_id = '" + CustomerID + "' and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                //SqlCommand cmd = new SqlCommand("select  customer_id,customer_name, customer_address1, customer_address2,customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled from mast_customer_maint where customer_id = '" + CustomerID + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    lstCustomer = new List<Customer>();
                    while (reader.Read())
                    {
                        cust = new Customer
                        {
                            CustomerID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0),
                            CustomerName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1),
                            Address1 = reader.IsDBNull(2) ? string.Empty : reader.GetString(2),
                            Address2 = reader.IsDBNull(3) ? string.Empty : reader.GetString(3),
                            City = reader.IsDBNull(4) ? string.Empty : reader.GetString(4),
                            State = reader.IsDBNull(5) ? string.Empty : reader.GetString(5),
                            Country = reader.IsDBNull(6) ? string.Empty : reader.GetString(6),
                            ZipCode = reader.IsDBNull(7) ? string.Empty : reader.GetString(7),
                            TelephoneNumber = reader.IsDBNull(8) ? string.Empty : reader.GetString(8),
                            EmailAddress = reader.IsDBNull(9) ? string.Empty : reader.GetString(9),
                            IsEnabled = reader.IsDBNull(10) ? false : Convert.ToBoolean(reader.GetValue(10)),
                            IsActive = reader.IsDBNull(11) ? false : Convert.ToBoolean(reader.GetValue(11))
                        };
                        lstCustomer.Add(cust);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return lstCustomer;
        }

        public List<CustAccount> GetCustomerAccount(string CustomerID, string AccountNo)
        {
            //DataSet ds = new DataSet();
            List<CustAccount> accountLst = null;
            int columnIndx = 0;
            try
            {

                conn.Open();

                SqlCommand cmd;
                cmd = new SqlCommand("select  customer_id,account_number,account_name,bank_id from mast_customer_account_maint where customer_id = '" + CustomerID + "' and account_number = '" + AccountNo + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    accountLst = new List<CustAccount>();
                    while (reader.Read())
                    {
                        columnIndx = 0;
                        CustAccount account = new CustAccount();
                        account.CustID = reader.GetString(columnIndx++);
                        account.AccountNumber = reader.GetString(columnIndx++);
                        account.AccountName = reader.GetString(columnIndx++);
                        account.BankID = reader.GetString(columnIndx++);

                        accountLst.Add(account);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return accountLst;
        }


        public List<CustAccount> GetCustomerAccountRecords(string CustomerID)
        {
            //DataSet ds = new DataSet();
            List<CustAccount> accountLst = null;
            int columnIndx = 0;
            try
            {

                conn.Open();

                SqlCommand cmd;
                cmd = new SqlCommand("select  customer_id,account_number,account_name,bank_id from mast_customer_account_maint where customer_id = '" + CustomerID + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    accountLst = new List<CustAccount>();
                    while (reader.Read())
                    {
                        columnIndx = 0;
                        CustAccount account = new CustAccount();
                        account.CustID = reader.GetString(columnIndx++);
                        account.AccountNumber = reader.GetString(columnIndx++);
                        account.AccountName =reader.GetString(columnIndx++);
                        account.BankID =  reader.GetString(columnIndx++);

                        accountLst.Add(account);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return accountLst;
        }
        public DataTable GetRemitterAccount(string RemitterID, string AccountNo)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();

                SqlCommand cmd;
                cmd = new SqlCommand("select remitter_id,account_number,account_name,bank_id from mast_remitter_account_maint where remitter_id = '" + RemitterID + "' and account_number = '" + AccountNo + "'", conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "CustomerDetails");
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetCustomerDetailsByBankId(string BankID)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();

                SqlCommand cmd;
                if (string.IsNullOrEmpty(BankID))
                    cmd = new SqlCommand("select  bank_id,bank_name, bank_ifsc, is_enabled from mast_bank_maint where bank_id like '" + BankID + "%'", conn);
                else
                    cmd = new SqlCommand("select bank_id,bank_name, bank_ifsc, is_enabled from mast_bank_maint where bank_id = '" + BankID + "'", conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "BankDetails");
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }


        public List<Customer> GetAllCustomers()
        {
            List<Customer> customerList = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select  customer_id,customer_name, customer_address1, customer_address2,customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled from mast_customer_maint", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    customerList = new List<Customer>();
                    while (reader.Read())
                    {
                        Customer customer = new Customer
                        {
                            CustomerID = reader.GetString(0),
                            CustomerName = reader.GetString(1),
                            Address1 = (reader.IsDBNull(2) ? string.Empty : reader.GetString(2)),
                            Address2 = (reader.IsDBNull(3) ? string.Empty : reader.GetString(3)),
                            City = (reader.IsDBNull(4) ? string.Empty : reader.GetString(4)),
                            State = (reader.IsDBNull(5) ? string.Empty : reader.GetString(5)),
                            Country = (reader.IsDBNull(6) ? string.Empty : reader.GetString(6)),
                            ZipCode = (reader.IsDBNull(7) ? string.Empty : reader.GetString(7)),
                            TelephoneNumber = (reader.IsDBNull(8) ? string.Empty : reader.GetString(8)),
                            EmailAddress = (reader.IsDBNull(9) ? string.Empty : reader.GetString(9)),
                            IsEnabled = (reader.IsDBNull(10) ? false: Convert.ToBoolean(reader.GetValue(10)))
                        };
                        customerList.Add(customer);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return customerList;
        }

        public List<Remitter> GetRemitterDetailsByRemitterId(string remitterID, string customerID)
        {
            List<Remitter> lstRemitter = null;

            try
            {
                SqlCommand cmd = new SqlCommand("select remitter_id,remitter_name,customer_id, address1, address2,address3,city,state,country,zipcode,tel_no,email_id,is_enable,discount_code,penalty_code,is_active from mast_remitter_maint where remitter_id = '" + remitterID + "' and customer_id = '" + customerID + "' and is_enable = 1 and is_active = 1", conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    lstRemitter = new List<Remitter>();
                    while (reader.Read())
                    {
                        Remitter remitter = new Remitter
                        {
                            RemitterID = reader.IsDBNull(0) ? string.Empty : Convert.ToString(reader.GetString(0)),
                            RemitterName = reader.IsDBNull(1) ? string.Empty : Convert.ToString(reader.GetString(1)),
                            CustomerID = reader.IsDBNull(2) ? string.Empty : Convert.ToString(reader.GetString(2)),
                            Address1 = reader.IsDBNull(3) ? string.Empty : Convert.ToString(reader.GetString(3)),
                            Address2 = reader.IsDBNull(4) ? string.Empty : Convert.ToString(reader.GetString(4)),
                            Address3 = reader.IsDBNull(5) ? string.Empty : Convert.ToString(reader.GetString(5)),
                            City = reader.IsDBNull(6) ? string.Empty : Convert.ToString(reader.GetString(6)),
                            State = reader.IsDBNull(7) ? string.Empty : Convert.ToString(reader.GetString(7)),
                            Country = reader.IsDBNull(8) ? string.Empty : Convert.ToString(reader.GetString(8)),
                            ZipCode = reader.IsDBNull(9) ? string.Empty : Convert.ToString(reader.GetString(9)),
                            TelephoneNumber = reader.IsDBNull(10) ? string.Empty : Convert.ToString(reader.GetString(10)),
                            EmailAddress = reader.IsDBNull(11) ? string.Empty : Convert.ToString(reader.GetString(11)),
                            IsEnabled = reader.IsDBNull(12) ? false : Convert.ToBoolean(reader.GetByte(12)),
                            DiscountCode = reader.IsDBNull(13) ? string.Empty : Convert.ToString(reader.GetString(13)),
                            PenaltyCode = reader.IsDBNull(14) ? string.Empty : Convert.ToString(reader.GetString(14)),
                            IsActive = reader.IsDBNull(15) ? false : Convert.ToBoolean(reader.GetByte(15))
                        };
                        lstRemitter.Add(remitter);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstRemitter;
        }

        public List<Remitter> GetRemitterDetails(string RemitterId, string CustomerId)
        {
            List<Remitter> remitterLst = null;
            try
            {

                SqlCommand cmd;
                if (string.IsNullOrEmpty(RemitterId))
                    cmd = new SqlCommand("select remitter_id,remitter_name,customer_id, address1, address2,address3,city,state,country,zipcode,tel_no,email_id,is_enable,is_active,discount_code,penalty_code from mast_remitter_maint where remitter_id like  '" + RemitterId + "%' and customer_id = '" + CustomerId + "' and is_enable = 1 and is_active = 1 and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                else
                    cmd = new SqlCommand("select remitter_id,remitter_name,customer_id, address1, address2,address3,city,state,country,zipcode,tel_no,email_id,is_enable,is_active,discount_code,penalty_code from mast_remitter_maint where remitter_id =  '" + RemitterId + "' and customer_id = '" + CustomerId + "' and is_enable = 1 and is_active = 1 and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    remitterLst = new List<Remitter>();
                    while (reader.Read())
                    {
                        Remitter remitter = new Remitter
                        {
                            RemitterID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0),
                            RemitterName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1),
                            CustomerID = reader.IsDBNull(2) ? string.Empty : reader.GetString(2),
                            Address1 = reader.IsDBNull(3) ? string.Empty : Convert.ToString(reader.GetString(3)),
                            Address2 = reader.IsDBNull(4) ? string.Empty : Convert.ToString(reader.GetString(4)),
                            Address3 = reader.IsDBNull(5) ? string.Empty : Convert.ToString(reader.GetString(5)),
                            City = reader.IsDBNull(6) ? string.Empty : Convert.ToString(reader.GetString(6)),
                            State = reader.IsDBNull(7) ? string.Empty :Convert.ToString(reader.GetString(7)),
                            Country = reader.IsDBNull(8) ? string.Empty :Convert.ToString(reader.GetString(8)),
                            ZipCode = reader.IsDBNull(9) ? string.Empty : Convert.ToString(reader.GetString(9)),
                            TelephoneNumber = reader.IsDBNull(10) ? string.Empty : Convert.ToString(reader.GetString(10)),
                            EmailAddress = reader.IsDBNull(11) ? string.Empty : Convert.ToString(reader.GetString(11)),
                            IsEnabled = reader.IsDBNull(12) ? false : Convert.ToBoolean(reader.GetValue(12)),
                            IsActive = reader.IsDBNull(13) ? false : Convert.ToBoolean(reader.GetValue(13)),
                            DiscountCode = reader.IsDBNull(14) ? string.Empty : Convert.ToString(reader.GetString(14)),
                            PenaltyCode = reader.IsDBNull(15) ? string.Empty : Convert.ToString(reader.GetString(15))
                        };
                        remitterLst.Add(remitter);
                    }
                }

            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return remitterLst;
        }


        //public DataTable GetDiscountDetails(string DiscountCode, string CustomerId)
        //{
        //    DataSet ds = new DataSet();

        //    try
        //    {

        //        conn.Open();

        //        SqlCommand cmd = new SqlCommand("SELECT Discount_Code,Effective_Date,Days_Start,Days_End,Discount_Percentage,Min_Amount,Max_Amount FROM mast_discount_maint where Discount_Code like  '" + DiscountCode + "%' and customer_id = '" + CustomerId + "'", conn);
        //        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        //        adapter.Fill(ds, "CustomerDetails");
        //    }
        //    catch (Exception exc)
        //    {

        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }

        //    return ds.Tables[0];
        //}

        //public DataTable GetPenaltyDetails(string PenaltyCode, string CustomerId)
        //{
        //    DataSet ds = new DataSet();

        //    try
        //    {

        //        conn.Open();

        //        SqlCommand cmd = new SqlCommand("SELECT Panalty_Code,Effective_Date,Days_Start,Days_End,Panalty_Percentage,Min_Amount,Max_Amount FROM mast_panalty_maint where Panalty_Code like  '" + PenaltyCode + "%' and customer_id = '" + CustomerId + "'", conn);
        //        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        //        adapter.Fill(ds, "CustomerDetails");
        //    }
        //    catch (Exception exc)
        //    {

        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }

        //    return ds.Tables[0];
        //}

        public List<DiscountPenalty> GetDiscountDetails(string DiscountCode, string CustomerId)
        {
            List<DiscountPenalty> discPenList = null;

            try
            {

                conn.Open();

                //SqlCommand cmd = new SqlCommand("SELECT Discount_Code,Effective_Date,Days_Start,Days_End,Discount_Percentage,Min_Amount,Max_Amount FROM mast_discount_maint where Discount_Code like  '" + DiscountCode + "%' and customer_id = '" + CustomerId + "'", conn);

                SqlCommand cmd = new SqlCommand("SELECT mst.discount_code,mst.effective_date,conf.days_start,conf.days_end,conf.discount_percentage,mst.min_amount,mst.max_amount,conf.Id " +
                    " FROM mast_discount_maint mst INNER JOIN conf_DiscountPenalty_Range conf ON conf.code = mst.discount_code " +
                    " where mst.Discount_Code like  '" + DiscountCode + "%' and mst.customer_id = '" + CustomerId + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    discPenList = new List<DiscountPenalty>();
                    while (reader.Read())
                    {
                        DiscountPenalty discPen = new DiscountPenalty
                        {
                            discountPenalty = reader.GetString(0),
                            effectiveDate = reader.GetDateTime(1),
                            daySatrt = (reader.IsDBNull(2) ? 0 : reader.GetInt32(2)),
                            dayEnd = (reader.IsDBNull(3) ? 0 : reader.GetInt32(3)),
                            percentage = (reader.IsDBNull(4) ? 0.00 : reader.GetDouble(4)),
                            minAmount = (reader.IsDBNull(5) ? 0.00 : reader.GetDouble(5)),
                            maxAmount = (reader.IsDBNull(6) ? 0.00 : reader.GetDouble(6)),
                            autoIncrementId = (reader.IsDBNull(7) ? 0 : reader.GetInt32(7))
                        };
                        discPenList.Add(discPen);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return discPenList;
        }

        public List<DiscountPenalty> GetPenaltyDetails(string PenaltyCode, string CustomerId)
        {
            List<DiscountPenalty> discPenList = null;

            try
            {

                conn.Open();

                //SqlCommand cmd = new SqlCommand("SELECT Panalty_Code,Effective_Date,Days_Start,Days_End,Panalty_Percentage,Min_Amount,Max_Amount FROM mast_panalty_maint where Panalty_Code like  '" + PenaltyCode + "%' and customer_id = '" + CustomerId + "'", conn);

                SqlCommand cmd = new SqlCommand("SELECT mst.Panalty_Code,mst.Effective_Date,conf.Days_Start,conf.Days_End,conf.discount_percentage,mst.Min_Amount,mst.Max_Amount,conf.Id  " +
                    " FROM mast_panalty_maint  mst INNER JOIN conf_DiscountPenalty_Range conf ON conf.code = mst.panalty_code " +
                    " where mst.Panalty_Code like  '" + PenaltyCode + "%' and mst.customer_id = '" + CustomerId + "'", conn);

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    discPenList = new List<DiscountPenalty>();
                    while (reader.Read())
                    {
                        DiscountPenalty discPen = new DiscountPenalty
                        {
                            discountPenalty = reader.GetString(0),
                            effectiveDate = reader.GetDateTime(1),
                            daySatrt = (reader.IsDBNull(2) ? 0 : reader.GetInt32(2)),
                            dayEnd = (reader.IsDBNull(3) ? 0 : reader.GetInt32(3)),
                            percentage = (reader.IsDBNull(4) ? 0.00 : reader.GetDouble(4)),
                            minAmount = (reader.IsDBNull(5) ? 0.00 : reader.GetDouble(5)),
                            maxAmount = (reader.IsDBNull(6) ? 0.00 : reader.GetDouble(6)),
                            autoIncrementId = (reader.IsDBNull(7) ? 0 : reader.GetInt32(7))
                        };
                        discPenList.Add(discPen);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return discPenList;
        }

        public UserRole GetRoleDetailsByRoleId(string RoleID, int option)
        {
            UserRole role = null;

            try
            {
                SqlCommand cmd;
                conn.Open();
                if (option == 2)
                    cmd = new SqlCommand("SELECT Role_Id,Role_Desc,Role_Type,Is_Enabled FROM mast_role_maint WHERE Role_ID like '" + RoleID + "%'", conn);
                else
                    cmd = new SqlCommand("SELECT Role_Id,Role_Desc,Role_Type,Is_Enabled FROM mast_role_maint WHERE Role_ID = '" + RoleID + "'", conn);

                //SqlCommand cmd = new SqlCommand("SELECT Role_Id,Role_Desc,Role_Type,Is_Enabled FROM mast_role_maint WHERE Role_ID = '" + RoleID + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        role = new UserRole
                        {
                            RoleID = reader.GetString(0),
                            RoleDescription = reader.GetString(1),
                            RoleType = reader.GetString(2),
                            IsEnabled = (reader.GetByte(3) == 1)
                        };
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return role;
        }
        public UserRole GetRoleDetailsByRoleId(string RoleID)
        {
            UserRole role = null;

            try
            {

                conn.Open();

                SqlCommand cmd = new SqlCommand("SELECT Role_Id,Role_Desc,Role_Type,Is_Enabled FROM mast_role_maint WHERE Role_ID = '" + RoleID + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        role = new UserRole
                        {
                            RoleID = reader.IsDBNull(0) ? string.Empty : Convert.ToString(reader.GetString(0)),
                            RoleDescription = reader.IsDBNull(1) ? string.Empty : Convert.ToString(reader.GetString(1)),
                            RoleType = reader.IsDBNull(2) ? string.Empty : Convert.ToString(reader.GetString(2)),
                            IsEnabled = reader.IsDBNull(3) ? false : Convert.ToBoolean(reader.GetValue(3))
                        };
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return role;
        }

        public List<MenuItem> GetMenuForRole(string RoleID)
        {
            List<MenuItem> menus = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT role_id,menu_id FROM mast_role_menu_maint where role_id = '" + RoleID + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    menus = new List<MenuItem>();
                    while (reader.Read())
                    {
                        MenuItem menu = new MenuItem
                        {
                            RoleId = reader.IsDBNull(0) ? string.Empty : reader.GetString(0),
                            MenuID = reader.IsDBNull(1) ? string.Empty : reader.GetString(1)
                        };
                        menus.Add(menu);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return menus;
        }

        public List<UserRole> GetAllRoles(bool systemIncluded, string custId)
        {
            List<UserRole> roles = null;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT Role_Id,Role_Desc,Role_Type,Is_Enabled FROM mast_role_maint" + (systemIncluded ? "" : " WHERE role_type != 'S' and customer_id = '"+ custId +"'"), conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    roles = new List<UserRole>();
                    while (reader.Read())
                    {
                        UserRole role = new UserRole
                        {
                            RoleID = reader.IsDBNull(0)?string.Empty:reader.GetString(0),
                            RoleDescription = reader.IsDBNull(1) ? string.Empty : reader.GetString(1),
                            RoleType = reader.IsDBNull(2) ? string.Empty : reader.GetString(2),
                            IsEnabled = (reader.GetByte(3) == 1)
                        };
                        roles.Add(role);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return roles;
        }


        public List<MenuItem> GetAllMenus(bool systemIncluded)
        {
            List<MenuItem> menus = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT menu_id,menu_name FROM conf_menu_maint where menu_link is not null and menu_link <> ''" + (systemIncluded ? "" : " and menu_type <> 'S'"), conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    menus = new List<MenuItem>();
                    while (reader.Read())
                    {
                        MenuItem menu = new MenuItem
                        {
                            MenuID = reader.GetString(0),
                            MenuName = reader.GetString(1),
                        };
                        menus.Add(menu);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return menus;
        }
        public List<Remitter> GetRemitterIdForMatchingCriteria(string customerId)
        {
            List<Remitter> remitters = null;

            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("SELECT remitter_id,remitter_name FROM mast_remitter_maint where customer_id =  '" + customerId + "' and is_active = '1' and is_enable = '1' and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    remitters = new List<Remitter>();
                    while (reader.Read())
                    {
                        Remitter remitter = new Remitter
                        {
                            RemitterID = reader.GetString(0),
                            RemitterName = reader.GetString(1)
                        };
                        remitters.Add(remitter);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return remitters;
        }

        public List<Customer> GetCustomer(string customerId)
        {
            List<Customer> customerList = null;

            try
            {

                conn.Open();
                SqlCommand cmd;
                if (customerId.Equals(string.Empty))
                {
                    cmd = new SqlCommand("SELECT customer_id,customer_name FROM mast_customer_maint where is_active = '1' and is_enabled = '1' and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                }
                else
                {
                    cmd = new SqlCommand("SELECT customer_id,customer_name FROM mast_customer_maint where customer_id =  '" + customerId + "' and is_active = '1' and is_enabled = '1' and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                }
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    customerList = new List<Customer>();
                    while (reader.Read())
                    {
                        Customer customer = new Customer
                        {
                            CustomerID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0),
                            CustomerName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1)
                        };
                        customerList.Add(customer);
                    }
                }


            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return customerList;
        }

        public List<Remitter> GetRemitter(string remitterId)
        {
            DataSet ds = new DataSet();
            List<Remitter> remitterLst = null;
            try
            {
                conn.Open();
                SqlCommand cmd;
                if (remitterId.Equals(string.Empty))
                {
                    cmd = new SqlCommand("select remitter_id,remitter_name from mast_remitter_maint where is_active = '1' and is_enable = '1' and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                }
                else
                {
                    cmd = new SqlCommand("select remitter_id,remitter_name from mast_remitter_maint where remitter_id =  '" + remitterId + "' and is_active = '1' and is_enable = '1' and (authby_id is not null or authby_id  <> '') and (authby_datetime is not null or authby_datetime <> '')", conn);
                }
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    remitterLst = new List<Remitter>();
                    while (reader.Read())
                    {
                        Remitter remitter = new Remitter
                        {
                            RemitterID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0),
                            RemitterName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1)
                        };
                        remitterLst.Add(remitter);
                    }
                }


            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return remitterLst;
        }
        public DataTable GetReportData(ReportSearchCriteria criteria)
        {
            DataTable resultData = null;

            try
            {
                SqlCommand cmd = new SqlCommand();
                StringBuilder sql = new StringBuilder();

                sql.Append("SELECT ");

                if (criteria.FieldList.Count > 0)
                {
                    foreach (KeyValuePair<string, string> item in criteria.FieldList)
                    {
                        sql.Append(item.Key + ", ");
                    }
                }
                else
                {
                    sql.Append(" *   ");
                }
                sql.Remove(sql.Length - 2, 1);

                switch (criteria.ReportType)
                {
                    case ReportSearchCriteria.Type.Invoice:
                        sql.Append("FROM pro_invoice ");
                        AddFilter(criteria, "customer_id", "remitter_id", "date", "invoic_status", ref sql, ref cmd);
                        break;
                    case ReportSearchCriteria.Type.Statement:
                        sql.Append(" FROM pro_statement ");
                        AddFilter(criteria, "customer_id", "", "value_date", "statement_status", ref sql, ref cmd);
                        break;
                    case ReportSearchCriteria.Type.Both:
                        sql.Append(" FROM pro_invoice, pro_statement ");
            if (!string.IsNullOrEmpty(criteria.CustomerID) && criteria.CustomerID != "SYSTEM")
            {
                sql.Append("WHERE pro_invoice.Customer_ID = @customerID AND ");
                SqlParameter custParam = new SqlParameter("@customerID", SqlDbType.VarChar, 50);
                custParam.Value = criteria.CustomerID;
                cmd.Parameters.Add(custParam);
            }
            if (!string.IsNullOrEmpty(criteria.RemitterID) && criteria.RemitterID != "SYSTEM")
            {
                sql.Append("WHERE pro_invoice.Remitter_ID = @remitterID AND ");
                SqlParameter remitParam = new SqlParameter("@remitterID", SqlDbType.VarChar, 50);
                remitParam.Value = criteria.RemitterID;
                cmd.Parameters.Add(remitParam);
            }
                        if (criteria.FromDate != DateTime.MinValue && criteria.ToDate != DateTime.MinValue)
                        {
                            sql.Append("WHERE date BETWEEN @fromDate AND @toDate && value_date BETWEEN @fromDate AND @toDate");
                        }
                        else if (criteria.FromDate != DateTime.MinValue)
                        {
                            sql.Append("WHERE date >= @fromDate && value_date >= @fromDate");
                        }
                        else if (criteria.ToDate != DateTime.MinValue)
                        {
                            sql.Append("WHERE date <= @toDate && value_date <= @toDate");
                        }
                        break;
                }

                cmd.Connection = conn;
                cmd.CommandText = sql.ToString();
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    resultData = new DataTable();
                    int x = 0;
                    if (criteria.FieldList.Count > 0)
                    {
                        foreach (KeyValuePair<string, string> item in criteria.FieldList)
                        {
                            DataColumn col = new DataColumn(item.Key, reader.GetFieldType(x));
                            col.Caption = item.Value;
                            resultData.Columns.Add(col);
                            x++;
                        }
                    }
                    else
                    {
                        if (criteria.ReportType.ToString() == ReportType.Invoice.ToString())
                        {
                            for (int i = 0; i <= reader.FieldCount - 1; i++)
                            {
                                //invoice_number:Invoice Number,date:Invoice Date,remitter_id:Remitter ID,customer_id:Customer ID,currency:Currency,amount:Amount,po_ref:PO Reference Number,invoic_status:Invoice Status,is_partial:Partial
                                DataColumn col = new DataColumn("Column" + i);
                                resultData.Columns.Add(col);
                            }
                        }
                        else if (criteria.ReportType.ToString() == ReportType.Statement.ToString())
                        {
                            for (int i = 0; i <= reader.FieldCount - 1; i++)
                            {
                                DataColumn col = new DataColumn("Column" + i);
                                resultData.Columns.Add(col);
                            }
                        }

                    }

                    while (reader.Read())
                    {
                        DataRow row = resultData.NewRow();
                        for (int ctr = 0; ctr <= reader.FieldCount - 1; ctr++)
                        {
                            row[ctr] = reader.GetValue(ctr);
                        }
                        resultData.Rows.Add(row);
                    }
                }
                reader.Close();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return resultData;
        }

        public Bank GetBank(string bankId)
        {
            Bank bank = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                if (string.IsNullOrEmpty(bankId))
                {
                    cmd.CommandText = "select bank_id,bank_name, bank_ifsc, is_enabled from mast_bank_maint where is_enabled = '1'";
                }
                else
                {
                    cmd.CommandText = "select bank_id,bank_name, bank_ifsc, is_enabled from mast_bank_maint where bank_id = '" + bankId + "' and is_enabled = '1'";
                }

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        bank = new Bank
                        {
                            BankID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0),
                            BankName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1),
                            BankIFSC = reader.IsDBNull(2) ? string.Empty : reader.GetString(2),
                            IsEnabled = reader.IsDBNull(3) ? false : Convert.ToBoolean(reader.GetString(3))
                        };
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return bank;
        }

        public List<string> GetAutoSequenceForMatchInvoice(string CustomerId, string RemitterId)
        {
            List<string> matchTypes = null;

            try
            {
                conn.Open();

                //select t1.MatchingType from mast_automatchingsequnce as t1,conf_automatchingsequence as t2 where t1.Sequence_Id = t2.Sequence_Id and t2.customer_Id = '001' and t2.remitter_Id = 'R01' order by t2.Id asc;
                //comment on 29th Jan 2014
                //SqlCommand cmd = new SqlCommand("select t1.MatchingType from mast_automatchingsequnce as t1,conf_automatchingsequence as t2 where t1.Sequence_Id = t2.Sequence_Id and t2.customer_Id = '" + CustomerId + "' and t2.remitter_Id = '" + RemitterId + "'  order by t2.Id asc", conn);
                SqlCommand cmd = new SqlCommand("select t1.MatchingType from mast_automatchingsequnce as t1,conf_automatchingsequence as t2 where t1.Sequence_Id = t2.Sequence_Id and t2.customer_Id = '" + CustomerId + "' " +
                    (RemitterId != "0" ? " AND t2.remitter_Id = '" + RemitterId + "' order by t2.Id asc" : ""), conn);
                //"WHERE i.customer_id = '" + CustomerID + "'" +
                //   (RemitterId != "0" ? " AND r.remitter_id = '" + RemitterId + "'" : "") + 
                //   " AND invoic_status IN ('N', 'U', 'P', 'R')", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    matchTypes = new List<string>();
                    while (reader.Read())
                    {
                        //single column so add directly to the list - no object creation required
                        matchTypes.Add(reader.IsDBNull(0) ? string.Empty : reader.GetString(0));
                    }
                }
                reader.Close();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return matchTypes;
        }

        public List<Customer> GetUnauthorizedCustomers(string UserId)
        {
            List<Customer> customers = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT customer_id,customer_name, customer_address1,customer_city,customer_state, customer_country,customer_emailid FROM mast_customer_maint  where (authby_id is null or authby_id  = '') and (authby_datetime is null) and (addby_id <> '" + UserId + "') and (modby_id <> '" + UserId + "' or modby_id is null or modby_id = '')", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    customers = new List<Customer>();
                    while (reader.Read())
                    {
                        Customer cust = new Customer
                        {
                            CustomerID = reader.IsDBNull(0) ? string.Empty : Convert.ToString(reader.GetString(0)),
                            CustomerName = reader.IsDBNull(1) ? string.Empty : Convert.ToString(reader.GetString(1)),
                            Address1 = reader.IsDBNull(2) ? string.Empty : Convert.ToString(reader.GetString(2)),
                            City = reader.IsDBNull(3) ? string.Empty : Convert.ToString(reader.GetString(3)),
                            State = reader.IsDBNull(4) ? string.Empty : Convert.ToString(reader.GetString(4)),
                            Country = reader.IsDBNull(5) ? string.Empty : Convert.ToString(reader.GetString(5)),
                            EmailAddress = reader.IsDBNull(6) ? string.Empty : Convert.ToString(reader.GetString(6))
                        };
                        customers.Add(cust);
                    }
                }

                reader.Close();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return customers;
        }

        public List<Remitter> GetUnauthorizedRemitters(string UserId)
        {
            List<Remitter> remitters = null;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT remitter_id,remitter_name, address1,city,state,country,email_id,discount_code,penalty_code FROM mast_remitter_maint  where (authby_id is null or authby_id  = '') and (authby_datetime is null) and (addby_id <> '" + UserId + "') and (modby_id <> '" + UserId + "' or modby_id is null or modby_id = '')", conn);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    remitters = new List<Remitter>();
                    while (reader.Read())
                    {
                        Remitter remitter = new Remitter
                        {
                            RemitterID = reader.IsDBNull(0) ? string.Empty : Convert.ToString(reader.GetString(0)),
                            RemitterName = reader.IsDBNull(1) ? string.Empty : Convert.ToString(reader.GetString(1)),
                            Address1 = reader.IsDBNull(2) ? string.Empty : Convert.ToString(reader.GetString(2)),
                            City = reader.IsDBNull(3) ? string.Empty : Convert.ToString(reader.GetString(3)),
                            State = reader.IsDBNull(4) ? string.Empty : Convert.ToString(reader.GetString(4)),
                            Country = reader.IsDBNull(5) ? string.Empty : Convert.ToString(reader.GetString(5)),
                            EmailAddress = reader.IsDBNull(6) ? string.Empty : Convert.ToString(reader.GetString(6)),
                            DiscountCode = reader.IsDBNull(7) ? string.Empty : Convert.ToString(reader.GetString(7)),
                            PenaltyCode = reader.IsDBNull(8) ? string.Empty : Convert.ToString(reader.GetString(8))
                        };
                        remitters.Add(remitter);
                    }
                }

                reader.Close();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return remitters;
        }

        public List<User> GetUnauthorizedUsers(string UserId, string userType, string customerId)
        {
            List<User> users = null;

            try
            {
                conn.Open();
                //[zujar-12.4.2014] removed user type filter as customer admin should be able to see customer as well as remitter users
                SqlCommand cmd = new SqlCommand("SELECT User_Id,User_Name,Customer_Id,Email_Id,Mobile_No FROM mast_user_maint  where (AuthBy_UserId is null or AuthBy_UserId  = '') and (AuthBy_Date is null) and (AddBy_UserId <> '" + UserId + "') and (ModBy_UserId <> '" + UserId + "' or ModBy_UserId is null or ModBy_UserId = '') and Customer_Id = '" + customerId + "' and User_Id <> '" + UserId + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    users = new List<User>();
                    while (reader.Read())
                    {
                        User user = new User
                        {
                            UserID = reader.IsDBNull(0) ? string.Empty : Convert.ToString(reader.GetString(0)),
                            UserName = reader.IsDBNull(1) ? string.Empty : Convert.ToString(reader.GetString(1)),
                            CustomerID = reader.IsDBNull(2) ? string.Empty : Convert.ToString(reader.GetString(2)),
                            EmailAddress = reader.IsDBNull(3) ? string.Empty : Convert.ToString(reader.GetString(3)),
                            MobileNumber = reader.IsDBNull(4) ? string.Empty : Convert.ToString(reader.GetString(4))
                        };
                        users.Add(user);
                    }
                }

                reader.Close();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return users;
        }

        public int AuthorizeCustomers(string userID, string custID)
        {
            int rowCount = 0;
            try
            {
                string strQuery = "update mast_customer_maint set " +
                    "authby_id='" + userID + "', authby_datetime='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                    "where Customer_ID IN (" + custID + ")";

                conn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, conn);
                rowCount = cmd.ExecuteNonQuery();

                //Authorize default customer admin users - user id = customer id + "admin"
                if (rowCount > 0)
                {
                    StringBuilder userIDList = new StringBuilder();
                    string[] strCustID = custID.Split(',');
                    for (int x = 0; x < strCustID.Length; x++)
                    {
                        userIDList.Append("'" + strCustID[x].Replace("'", "") + "admin1',");
                        userIDList.Append("'" + strCustID[x].Replace("'", "") + "admin2',");
                    }
                    userIDList.Remove(userIDList.Length - 1, 1);

                    strQuery = "update mast_user_maint set is_enabled=1,is_active=1,Is_ForcedPwdChange=1," +
                        "AuthBy_UserId='" + userID + "', AuthBy_Date='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                    "where Customer_Id IN(" + custID + ") AND User_Id IN ('admin1', 'admin2')";
                        //"where User_Id IN (" + userIDList.ToString() + ")";

                    cmd.CommandText = strQuery;
                    rowCount = cmd.ExecuteNonQuery();

                    //send email to admin users of authorized customers with sign-in credentials
                    if (rowCount > 0)
                    {
                        cmd.CommandText = "SELECT Customer_ID, User_ID, User_Password, Email_ID FROM mast_user_maint WHERE Customer_Id IN(" + custID + ") AND User_ID IN ('admin1', 'admin2') AND is_enabled=1 AND is_active=1";
                        //cmd.CommandText = "SELECT Customer_ID, User_ID, User_Password, Email_ID FROM mast_user_maint WHERE User_ID IN (" + userIDList.ToString() + ") AND is_enabled=1 AND is_active=1";
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Utils.sendEmail("<b>Customer authorized successfully.</b><br /><br />The credentials for admin user account are as follows:<table><tr><td>Customer ID:</td><td>" + reader.GetString(0) + "</td></tr><tr><td>User ID:</td><td>" + reader.GetString(1) + "</td></tr><tr><td>Password:</td><td>" + Utils.DecryptValue(reader.GetString(2)) + "</td></tr></table><br /><br />you would be required to change the password upon first sign-in", "Customer Authorization Successful", reader.GetString(3));
                            }
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return rowCount;
        }

        public int AuthorizeRemitters(string userID, string remitterId)
        {
            int rowCount = 0;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                string strQuery = "update mast_remitter_maint set " +
                    "authby_id='" + userID + "', authby_datetime='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                    "where remitter_id IN (" + remitterId + ")";

                cmd.CommandText = strQuery;
                rowCount = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }
            return rowCount;
        }

        public int AuthorizeUsers(string userID, List<User> lstUsers)
        {
            int rowCount = 0;

            try
            {
                StringBuilder strSQL = new StringBuilder();
                strSQL.Append("BEGIN ");
                foreach (User user in lstUsers)
                {
                    user.Password = Utils.GenerateRandomPassword();
                    strSQL.Append("update mast_user_maint set User_Password='" + Utils.EncryptValue(user.Password) + "',Is_Enabled=1,Is_Active=1,Is_ForcedPwdChange=1," +
                                         "AuthBy_UserId='" + userID + "', AuthBy_Date='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                                         "where User_Id='" + user.UserID + "' AND Customer_Id='" + user.CustomerID + "';");
                }
                strSQL.Append(" END;");
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL.ToString(), conn);
                rowCount = cmd.ExecuteNonQuery();

                //if successfully authorized then send email to users
                if (rowCount > 0)
                {
                    foreach (User user in lstUsers)
                    {
                        string EmailBody = "Hi " + user.UserName + ",<br /><br /> Your 4circlesolutions.com credentials is:<br /><br /><table><tr><td>Customer ID:</td><td>" + user.CustomerID + "</td></tr><tr><td>User ID :</td><td>" + user.UserID +
                            "</td></tr><tr> <td>Password :</td><td>" + user.Password + "</td></tr></table><br /><br />you would be required to change the password upon first sign-in.";
                        Utils.sendEmail(EmailBody, "4circlesolutions credentials", user.EmailAddress);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return rowCount;
        }

        private void AddFilter(ReportSearchCriteria criteria, string customerIDColumn, string remitterIDColumn, string dateColumn, string statusColumn, ref StringBuilder sql, ref SqlCommand cmd)
        {
            if (!string.IsNullOrEmpty(criteria.CustomerID) && criteria.CustomerID != "SYSTEM")
            {
                sql.Append("WHERE " + customerIDColumn + " = @customerID AND ");
                SqlParameter custParam = new SqlParameter("@customerID", SqlDbType.VarChar, 50);
                custParam.Value = criteria.CustomerID;
                cmd.Parameters.Add(custParam);
            }
            if (!string.IsNullOrEmpty(criteria.RemitterID) && criteria.RemitterID != "SYSTEM")
            {
                if (!string.IsNullOrEmpty(remitterIDColumn))
                {
                    sql.Append("WHERE " + remitterIDColumn + " = @remitterID AND ");
                    SqlParameter remitParam = new SqlParameter("@remitterID", SqlDbType.VarChar, 50);
                    remitParam.Value = criteria.RemitterID;
                    cmd.Parameters.Add(remitParam);
                }
            }
            if (criteria.FromDate != DateTime.MinValue && criteria.ToDate != DateTime.MinValue)
            {
                sql.Append("WHERE " + dateColumn + " BETWEEN @fromDate AND @toDate AND ");
                SqlParameter fromParam = new SqlParameter("@fromDate", SqlDbType.DateTime);
                fromParam.Value = criteria.FromDate;
                cmd.Parameters.Add(fromParam);
                SqlParameter toParam = new SqlParameter("@toDate", SqlDbType.DateTime);
                toParam.Value = criteria.ToDate;
                cmd.Parameters.Add(toParam);
            }
            else if (criteria.FromDate != DateTime.MinValue)
            {
                sql.Append("WHERE " + dateColumn + " >= @fromDate AND ");
                SqlParameter fromParam = new SqlParameter("@fromDate", SqlDbType.DateTime);
                fromParam.Value = criteria.FromDate;
                cmd.Parameters.Add(fromParam);
            }
            else if (criteria.ToDate != DateTime.MinValue)
            {
                sql.Append("WHERE " + dateColumn + " <= @toDate AND ");
                SqlParameter toParam = new SqlParameter("@toDate", SqlDbType.DateTime);
                toParam.Value = criteria.ToDate;
                cmd.Parameters.Add(toParam);
            }
            if (criteria.Statuses != null && criteria.Statuses.Count<ReportSearchCriteria.Status>() > 0)
            {
                sql.Append("WHERE (" + statusColumn + " IN(");
                foreach (ReportSearchCriteria.Status status in criteria.Statuses)
                {
                    sql.Append("'" + status.ToString().Substring(0, 1) + "', ");
                }
                sql.Replace(",", "", sql.Length - 2, 1);
                sql.Append(")");

                if (criteria.Statuses.Contains(ReportSearchCriteria.Status.PartiallyMatched))
                {
                    sql.Append(" OR (" + statusColumn + " = 'P' AND is_partial = 1))");
                }
                else
                {
                    sql.Append(")");
                }
            }
            else
            {
                sql.Remove(sql.Length - 4, 4);
            }
            sql.Replace("AND WHERE", "AND ");
        }

        public List<DashboardData> GetDashboardData(Recon.Portal.DataObjects.User user)
        {
            List<DashboardData> dashboardList = null;

            try
            {
                string invoiceStatus = string.Empty, custRem = string.Empty;
                switch (user.UserType.ToUpper())
                {
                    case "C":
                            invoiceStatus = "'N', 'U', 'P', 'R'";
                        custRem = "customer_id = '" + user.CustomerID + "'";
                        break;
                    case "R":
                        invoiceStatus = "'N', 'U', 'P', 'R', 'D'";
                        custRem = "customer_id = '" + user.CustomerID + "' AND remitter_id = '" + user.RemitterID + "'";
                        break;
                    case "S":
                        break;
                }

                if (!string.IsNullOrEmpty(invoiceStatus))
                {
                    string strSQL = "SELECT 0 as FromDays, 30 as ToDays, 1000000001.00 as FromAmount, 0.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 0 and 30 AND amount > 1000000000.00 " +
                    "UNION ALL " +
                    "SELECT 0 as FromDays, 30 as ToDays, 500000001.00 as FromAmount, 1000000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 0 and 30 AND amount BeTWEEN 500000000.00 AND 1000000000.00 " +
                    "UNION ALL " +
                    "SELECT 0 as FromDays, 30 as ToDays, 10000001.00 as FromAmount, 500000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 0 and 30 AND amount BeTWEEN 10000001.00 AND 500000000.00 " +
                    "UNION ALL " +
                    "SELECT 0 as FromDays, 30 as ToDays, 5000001.00 as FromAmount, 10000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 0 and 30 AND amount BeTWEEN 5000001.00 AND 10000000.00 " +
                    "UNION ALL " +
                    "SELECT 0 as FromDays, 30 as ToDays, 100001.00 as FromAmount, 5000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE customer_id = '" + user.CustomerID + "' AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 0 and 30 AND amount BeTWEEN 100001.00 AND 5000000.00 " +
                    "UNION ALL " +
                    "SELECT 0 as FromDays, 30 as ToDays, 0.00 as FromAmount, 100000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 0 and 30 AND amount < 100000.00 " +
                    "UNION ALL " +
                        "SELECT 31 as FromDays, 90 as ToDays, 1000000001.00 as FromAmount, 0 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 31 and 90 AND amount > 1000000000.00 " +
                    "UNION ALL " +
                        "SELECT 31 as FromDays, 90 as ToDays, 500000001.00 as FromAmount, 1000000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 31 and 90 AND amount BeTWEEN 500000000.00 AND 1000000000.00 " +
                                        "UNION ALL " +
                        "SELECT 31 as FromDays, 90 as ToDays, 10000001.00 as FromAmount, 500000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 31 and 90 AND amount BeTWEEN 10000001.00 AND 500000000.00 " +
                    "UNION ALL " +
                        "SELECT 31 as FromDays, 90 as ToDays, 5000001.00 as FromAmount, 10000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 31 and 90 AND amount BeTWEEN 5000001.00 AND 10000000.00 " +
                    "UNION ALL " +
                        "SELECT 31 as FromDays, 90 as ToDays, 100001.00 as FromAmount, 5000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 31 and 90 AND amount BeTWEEN 100001.00 AND 5000000.00 " +
                    "UNION ALL " +
                        "SELECT 31 as FromDays, 90 as ToDays, 0.00 as FromAmount, 100000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 31 and 90 AND amount < 100000.00 " +
                    "UNION ALL " +
                        "SELECT 91 as FromDays, 180 as ToDays, 1000000001.00 as FromAmount, 0 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 91 and 180 AND amount > 1000000000.00 " +
                    "UNION ALL " +
                        "SELECT 91 as FromDays, 180 as ToDays, 500000001.00 as FromAmount, 1000000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 91 and 180 AND amount BeTWEEN 500000000.00 AND 1000000000.00 " +
                                        "UNION ALL " +
                        "SELECT 91 as FromDays, 180 as ToDays, 10000001.00 as FromAmount, 500000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 91 and 180 AND amount BeTWEEN 10000001.00 AND 500000000.00 " +
                    "UNION ALL " +
                        "SELECT 91 as FromDays, 180 as ToDays, 5000001.00 as FromAmount, 10000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 91 and 180 AND amount BeTWEEN 5000001.00 AND 10000000.00 " +
                    "UNION ALL " +
                        "SELECT 91 as FromDays, 180 as ToDays, 100001.00 as FromAmount, 5000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 91 and 180 AND amount BeTWEEN 100001.00 AND 5000000.00 " +
                    "UNION ALL " +
                        "SELECT 91 as FromDays, 180 as ToDays, 0.00 as FromAmount, 100000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 91 and 180 AND amount < 100000.00 " +
                    "UNION ALL " +
                        "SELECT 181 as FromDays, 360 as ToDays, 1000000001.00 as FromAmount, 0 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 181 and 360 AND amount > 1000000000.00 " +
                    "UNION ALL " +
                        "SELECT 181 as FromDays, 360 as ToDays, 500000001.00 as FromAmount, 1000000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 181 and 360 AND amount BeTWEEN 500000000.00 AND 1000000000.00 " +
                                        "UNION ALL " +
                        "SELECT 181 as FromDays, 360 as ToDays, 10000001.00 as FromAmount, 500000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 181 and 360 AND amount BeTWEEN 10000001.00 AND 500000000.00 " +
                    "UNION ALL " +
                        "SELECT 181 as FromDays, 360 as ToDays, 5000001.00 as FromAmount, 10000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 181 and 360 AND amount BeTWEEN 5000001.00 AND 10000000.00 " +
                    "UNION ALL " +
                        "SELECT 181 as FromDays, 360 as ToDays, 100001.00 as FromAmount, 5000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 181 and 360 AND amount BeTWEEN 100001.00 AND 5000000.00 " +
                    "UNION ALL " +
                        "SELECT 181 as FromDays, 360 as ToDays, 0.00 as FromAmount, 100000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) between 181 and 360 AND amount < 100000.00 " +
                        "UNION ALL " +
                        "SELECT 361 as FromDays, 0 as ToDays, 1000000001.00 as FromAmount, 0 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) > 360 AND amount > 1000000000.00 " +
                    "UNION ALL " +
                        "SELECT 361 as FromDays, 0 as ToDays, 500000001.00 as FromAmount, 1000000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) > 360 AND amount BeTWEEN 500000000.00 AND 1000000000.00 " +
                                        "UNION ALL " +
                        "SELECT 361 as FromDays, 0 as ToDays, 10000001.00 as FromAmount, 500000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) > 360 AND amount BeTWEEN 10000001.00 AND 500000000.00 " +
                    "UNION ALL " +
                        "SELECT 361 as FromDays, 0 as ToDays, 5000001.00 as FromAmount, 10000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) > 360 AND amount BeTWEEN 5000001.00 AND 10000000.00 " +
                    "UNION ALL " +
                        "SELECT 361 as FromDays, 0 as ToDays, 100001.00 as FromAmount, 5000000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) > 360 AND amount BeTWEEN 100001.00 AND 5000000.00 " +
                    "UNION ALL " +
                        "SELECT 361 as FromDays, 0 as ToDays, 0.00 as FromAmount, 100000.00 as ToAmount, COUNT(1), SUM(amount) FROM pro_invoice WHERE " + custRem + " AND (invoic_status IN (" + invoiceStatus + ") OR is_partial = 1) and DATEDIFF(day, date, GETDATE()) > 360 AND amount < 100000.00 ";

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        dashboardList = new List<DashboardData>();
                        while (reader.Read())
                        {
                            DashboardData item = new DashboardData
                            {
                                FromDays = reader.GetInt32(0),
                                ToDays = reader.GetInt32(1),
                                FromAmount = Convert.ToDouble(reader.GetValue(2)),
                                ToAmount = Convert.ToDouble(reader.GetValue(3)),
                                InvoiceCount = reader.GetInt32(4),
                                TotalAmount = (reader.IsDBNull(5) ? 0 : Convert.ToDouble(reader.GetValue(5)))
                            };
                            dashboardList.Add(item);
                        }
                    }
                    reader.Close();
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return dashboardList;
        }

        private void CreateTestData()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            //delete existing data
            //cmd.CommandText = "delete from mast_user_maint";
            //cmd.ExecuteNonQuery();
            cmd.CommandText = "delete from pro_invoice";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "delete from pro_statement";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "delete from pro_matching";
            cmd.ExecuteNonQuery();

            //user
            /*cmd.CommandText = "insert into mast_user_maint(User_Id, User_Type, Remitter_Id, Customer_Id, User_Name, User_Password, Role_Id, Is_MailEnabled, Is_MobileEnabled, Email_Id, Mobile_No, Is_Enabled, Is_LoggedIn, Is_Locked, Is_Active, Login_Attempt, AuthBy_UserId, AuthBy_Date, Is_DomainUser)" +
                " values('ashpatel', 'C', 'R03', '001', 'Ashutosh Patel', 'pwd@1234', 'Customer', 1, 1, 'ashutosh.patel@somesite.com', '9311212345', 1, 0, 0, 1, 0, 'admin', '2014-01-25', 0)";
            cmd.ExecuteNonQuery();
            */
            //invoices
            cmd.CommandText = "insert into pro_invoice(remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, invoic_status, is_partial)" +
                "values('R001', 'C001', 'inv1', getdate(), 'INR', '2000000', 'PR1', 'N', 0)," +
            "('R001', 'C001', 'inv2', getdate(), 'INR', '52000', 'PR2', 'N', 0)," +
            "('R001', 'C001', 'inv3', getdate(), 'INR', '13000000', 'PR1', 'N', 0)," +
            "('R001', 'C001', 'inv4', getdate(), 'INR', '520', 'PR3', 'N', 0)," +
            "('R001', 'C001', 'inv5', getdate(), 'INR', '4000000', 'PR1', 'N', 0)," +
            "('R001', 'C001', 'inv6', getdate(), 'INR', '5000', 'PR2', 'N', 0)," +
            "('R001', 'C001', 'inv7', getdate(), 'INR', '1000000', 'PR1', 'N', 0)," +
            "('R001', 'C001', 'inv8', getdate(), 'INR', '7050', 'PR3', 'N', 0)";
            cmd.ExecuteNonQuery();

            //statements
            cmd.CommandText = "insert into pro_statement(customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial)" +
                "values('C001', 'B001', getdate(), getdate(), getdate(), 'N', 'payment against inv1', null, null, null, 2044120, 'T00100207052013', 0, 'N', 0)," +
                "('C001', 'B001', getdate(), getdate(), getdate(), 'N', 'payment against inv2', null, null, null, 52000, 'P002005407052013', 52000, 'N', 0)," +
                "('C001', 'B001', getdate(), getdate(), getdate(), 'C', 'payment against inv7', null, null, null, 1000000, 'C312345', 1000000, 'N', 0)," +
                "('C001', 'B002', getdate(), getdate(), getdate(), 'N', 'payment against inv8', null, null, null, 7050, 'J312345', 7050, 'N', 0)," +
                "('C001', 'B002', getdate(), getdate(), getdate(), 'N', 'R02', null, null, null, 13000000, 'NQS31302993', 0, 'N', 0)," +
                "('C001', 'B001', getdate(), getdate(), getdate(), 'N', 'from R02', null, null, null, 15930, 'NVR3030213', 0, 'N', 0)," +
                "('C001', 'B003', getdate(), getdate(), getdate(), 'N', 'payment against inv4', null, null, null, 7145, 'NVCS3120145', 0, 'N', 0)," +
                "('C001', 'B002', getdate(), getdate(), getdate(), 'N', 'payment against inv8', null, null, null, 3000000, 'K29301203', 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            conn.Close();
        }

    }
}